Click here to Skip to main content

Some Important SQL Queries

Mani Bhushan

4.64/5 (14 votes)

6 Nov 2011CPOL

39.2K

Some Important SQL Queries

List all the databases on SQL Servers.

You can do it by following System Stored Procedures and queries:

SQL

EXEC sp_databases

EXEC sp_helpdb

SELECT name
FROM sys.databases

SELECT name
FROM sys.sysdatabases

List all Tables in the database on SQL Servers.

You can find out all tables within a database by the following query:

SQL
```
SELECT *
FROM sys.Tables

SELECT * FROM information_schema.tables

SELECT * FROM sysobjects WHERE xtype='U'
```
Here is a list of other object types you can search for as well:

C: Check constraint
D: Default constraint
F: Foreign Key constraint
L: Log
P: Stored procedure
PK: Primary Key constraint
RF: Replication Filter stored procedure
S: System table
TR: Trigger
U: User table
UQ: Unique constraint
V: View
X: Extended stored procedure
List all Stored Procedures in the database on SQL Servers.

You can find out all Stored Procedures within a database by the following query:

SQL
```
SELECT name
FROM sys.objects
WHERE type = 'P'
If you want to find it within a period then;

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) <5
```
The above query will find out all Stored Procedures created within 5 days.
List all User Defined Functions in the database on SQL Servers.

You can find out all User Defined Functions within a database by the following query:

SQL
```
SELECT *
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
```

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)