Followings are the statements to display various objects of SQL Server.
--Data Models
--SQL SERVER 2000
SELECT * FROM dbo.dtproperties
GO
-- SQL SERVER 2005
SELECT * FROM dbo.sysdiagrams;
GO
--Tables
-- SQL SERVER 2000
SELECT * FROM dbo.sysobjects WHERE xtype = 'u' ORDER BY Name
GO
-- SQL SERVER 2005
SELECT * FROM sys.tables ORDER BY Name
GO
--Columns
-- SQL SERVER 2000
SELECT o.name, c.name FROM dbo.syscolumns c INNER JOIN dbo.sysobjects o ON c.id = o.id WHERE o.name = 'MyTableName' ORDER BY c.colorder
GO
-- SQL SERVER 2005
SELECT OBJECT_NAME([Object_ID]) AS 'TableName', [Name] AS 'ColumnName', Column_ID FROM sys.all_columns ORDER BY TableName, Column_ID
GO
-- Primary Keys
-- SQL SERVER 2000
SELECT p.name, OBJECT_NAME(parent_obj) AS 'Table Name' FROM dbo.sysobjects p WHERE p.xtype = 'PK' ORDER BY p.Name
GO
-- SQL SERVER 2005
SELECT OBJECT_NAME(o.parent_object_id) AS 'ParentObject', s.name AS 'Schema', o.Name AS 'PrimaryKey' FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.Type = 'PK' ORDER BY o.Name
GO
-- Foreign Keys
-- SQL SERVER 2000
SELECT OBJECT_NAME(f.constid) AS 'ForeignKey', OBJECT_NAME(f.fkeyid) AS 'FKTable', c1.[name] AS 'FKColumnName', OBJECT_NAME(f.rkeyid) AS 'PKTable', c2.[name] AS 'PKColumnName' FROM sysforeignkeys f INNER JOIN syscolumns c1 ON f.fkeyid = c1.[id] AND f.fkey = c1.colid INNER JOIN syscolumns c2 ON f.rkeyid = c2.[id] AND f.rkey = c2.colid ORDER BY OBJECT_NAME(f.rkeyid)
GO
-- SQL SERVER 2005
SELECT OBJECT_NAME(f.constraint_object_id) AS 'ForeignKey', OBJECT_NAME(f.parent_object_id) AS 'FKTable', c1.[name] AS 'FKColumnName', OBJECT_NAME(f.referenced_object_id) AS 'PKTable', c2.[name] AS 'PKColumnName' FROM sys.foreign_key_columns f INNER JOIN sys.all_columns c1 ON f.parent_object_id = c1.[object_id] AND f.parent_column_id = c1.column_id INNER JOIN sys.all_columns c2 ON f.referenced_object_id = c2.[object_id] AND f.referenced_column_id = c2.column_id ORDER BY OBJECT_NAME(f.referenced_object_id)
GO
-- Constraints
-- SQL SERVER 2000
SELECT o.[name] AS 'DefaultName', OBJECT_NAME(c.[id]) AS 'TableName', col.[name] AS 'ColumnName' FROM dbo.sysconstraints c INNER JOIN dbo.sysobjects o ON c.constid = o.[id] INNER JOIN dbo.syscolumns col ON col.[id] = c.colid ORDER BY o.[name]
GO
-- SQL SERVER 2005
SELECT OBJECT_NAME(o.parent_object_id) AS 'ParentObject', s.name AS 'Schema', o.Name AS 'PrimaryKey' FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.Type IN ('C', 'D', 'UQ') ORDER BY o.Name
GO
-- FileGroups\Partitions
-- SQL SERVER 2000
SELECT * FROM sysfilegroups
GO
-- SQL SERVER 2005
SELECT * FROM sys.data_spaces;
GO
-- Stored Procedures
-- SQL SERVER 2000
SELECT o.[name], o.[id], o.xtype, c.[text] FROM dbo.sysobjects o INNER JOIN dbo.syscomments c ON o.[id] = c.[id] WHERE o.xtype = 'p' ORDER BY o.[Name]
GO
-- SQL SERVER 2005
SELECT o.[Name], o.[object_id], o.[type], m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.[type] = 'p' ORDER BY o.[Name]
GO
-- Functions
-- SQL SERVER 2000
SELECT o.[name], o.[id], o.xtype, c.[text] FROM dbo.sysobjects o INNER JOIN dbo.syscomments c ON o.[id] = c.[id] WHERE o.xtype IN ('fn', 'if', 'tf') ORDER BY o.[Name]
GO
-- SQL SERVER 2005
SELECT o.[Name], o.[object_id], o.[type], m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.[type] IN ('fn', 'fs', 'ft', 'if', 'tf')ORDER BY o.[Name]
GO
-- Views
--SQL SERVER 2000
SELECT o.[name], o.[id], o.xtype, c.[text] FROM dbo.sysobjects o INNER JOIN dbo.syscomments c ON o.[id] = c.[id] WHERE o.xtype = 'v' ORDER BY o.[Name]
GO
--SQL SERVER 2005
SELECT o.[Name], o.[object_id], o.[type], m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.[type] = 'V' ORDER BY o.[Name]
GO
