SQl Server Tricks-Part1

Getting the List of all databases on a server

To get list of all database on a server, you can use this query.

USE master
GO
SELECT *
FROM sys.Databases
GO

OR you can use the sp_helpdb procedure for this.

Getting the List of all tables in a database

To get list of all tables in a database , you can use the same technique but just a bit different query.

USE YourDBName
GO
SELECT *
FROM sys.Tables
GO

Getting the List of all tables on a server 

For a list of all tables on a server, you can use the sp_msforeachdb procedure like this.

sp_msforeachdb @command1='USE ?;SELECT * FROM sys.Tables'

 

Getting the List of all procedures in a database

To get list of all procedures in a database , you can use the same technique but just a bit different query.

USE YourDBName
GO
SELECT *
FROM sys.procedures
GO