65.938 articles65.9K

CodeProject is changing. Read more.

Some Important SQL Queries

4.64/5 (13 votes)

Nov 1, 2011

39163

Some Important SQL Queries

List all the databases on SQL Servers. You can do it by following System Stored Procedures and queries:
```
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:
```
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:
```
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:
```
SELECT *
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
```