Thursday, May 1, 2008

Some Handy SQL Server Query

Get the Information about all the databases available in your SQL Server
EXEC sp_databases
EXEC sp_helpdb
select * from master..sysdatabases
SELECT * FROM sys.databases
SELECT * FROM sys.sysdatabases
EXEC sp_msForEachDB 'PRINT ''?'''
Get the Information about all the tables available in your SQL Server Database
SELECT Owner = TABLE_SCHEMA, TableName = TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0 ORDER BY TABLE_SCHEMA, TABLE_NAME
or
exec sp_tables
but with this you have to filter to exclude the table owned by (
TABLE_OWNER) 'sys','INFORMATION_SCHEMA'.
Get the Information about all the tables available in your SQL Server Database
sp_columns @tableName
Select * from Information_Schema.Columns where Table_Name=@tableName

No comments: