SQL with Manoj

While writing complex code & business logic in my scrits & stored-procdedures most of the time I wonder…
– What table does a particular column belongs to?
– What all Stored Procedures effect a particular table?
– How can I see what particular constraint does my tables have?
– What all Foreign Keys defined in table’s columns are linked to?

These and many more questions can be answered by querying the SQL Server System Catalog and metadata that SQL Server manages very beautifully.

The SQL Server system catalogs is a set of views that show metadata that describes the objects in an instance of SQL Server. Metadata is data that describes the attributes of objects in a system. SQL Server-based applications can access the information in the system catalogs by using some of the following:
-> Information Schema, views to quickly retrieve metadata
-> Catalog Views, recommened by MS.

–// Information Schema Views: They present the catalog information in a format that is independent of any catalog table implementation and therefore are not affected by changes in the underlying catalog tables.

-- INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.TABLES

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME		TABLE_TYPE
AdventureWorks	Production	ProductProductPhoto	BASE TABLE
AdventureWorks	Sales		StoreContact		BASE TABLE
AdventureWorks	Person		Address			BASE TABLE
AdventureWorks	Production	ProductReview		BASE TABLE
AdventureWorks	Production	TransactionHistory	BASE TABLE
AdventureWorks	Person		AddressType		BASE TABLE

select distinct TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES

TABLE_SCHEMA
dbo
HumanResources
Person
Production
Purchasing
Sales

select distinct TABLE_TYPE from INFORMATION_SCHEMA.TABLES

TABLE_TYPE
BASE TABLE
VIEW

-- INFORMATION_SCHEMA.COLUMNS
select * from INFORMATION_SCHEMA.COLUMNS
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
	CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH,
	NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE
from INFORMATION_SCHEMA.COLUMNS
select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'ContactID'
-- INFORMATION_SCHEMA.VIEWS

select * from INFORMATION_SCHEMA.VIEWS
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
-- INFORMATION_SCHEMA.ROUTINES
select * from INFORMATION_SCHEMA.ROUTINES
select distinct ROUTINE_SCHEMA from INFORMATION_SCHEMA.ROUTINES

ROUTINE_SCHEMA
dbo
HumanResources

select distinct ROUTINE_TYPE from INFORMATION_SCHEMA.ROUTINES

ROUTINE_TYPE
FUNCTION
PROCEDURE

select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, ROUTINE_BODY, ROUTINE_DEFINITION,
	IS_DETERMINISTIC, SQL_DATA_ACCESS, CREATED, LAST_ALTERED
from INFORMATION_SCHEMA.ROUTINES
select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like '%ContactID%'
select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS

–// Catalog Views: provide access to metadata that is stored in every database on the server.

-- The following SQL statement will pull all Stored-Procedures & Functions in the Database.
select c.id, object_name(c.id) as obj_name_c, o.name as obj_name_o, o.xtype, c.text, o.crdate, o.refdate
from sys.syscomments c
join sys.sysobjects o
on o.id = c.id
where xtype in ('P', 'FN')
-- This following SQL statement will pull all Objects information with in the Database.
select c.id, object_name(c.id) as obj_name_c, -- o.name
	case xtype
		when 'C' then 'Check Constraint'
		when 'D' then 'Default Constraint'
		when 'F' then 'Foreign Key Constraint'
		when 'L' then 'Log'
		when 'P' then 'Stored Procedure'
		when 'PK' then 'Primary Key Constraint'
		when 'RF' then 'Replication Filter Procedure'
		when 'S' then 'System Table'
		when 'TR' then 'Trigger'
		when 'U' then 'User Table'
		when 'UQ' then 'Unique Constraint'
		when 'V' then 'View'
		when 'X' then 'Extended Procedure'
		when 'FN' then 'User Defined Function'
		else 'N/A'
	end as obj_type,
	c.text, o.crdate, o.refdate
from sys.syscomments c
join sys.sysobjects o
on o.id = c.id

-- Pulls Foreign Key and its links
select fk.name as fk_name, fk.object_id, object_name(fk.parent_object_id) as table_name,
	col_name(fc.parent_object_id, fc.parent_column_id) as constraint_col_name,
	object_name(fk.referenced_object_id) as referenced_table,
	COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_col_name,
	fk.delete_referential_action_desc
from sys.foreign_keys fk
join sys.foreign_key_columns fc
on fk.object_id = fc.constraint_object_id

Note: All examples are executed on SQL Server 2005 AdventureWorks DB.

More info from MSDN BOL: http://msdn.microsoft.com/en-us/library/ms189082(v=SQL.90).aspx
MSDN FAQs: http://msdn.microsoft.com/en-us/library/ms345522.aspx