Advertisements
Home > DBA Stuff > SQL DBA – Querying SQL Server Metadata, INFORMATION_SCHEMA, sysObjects, System Catalog, etc

SQL DBA – Querying SQL Server Metadata, INFORMATION_SCHEMA, sysObjects, System Catalog, etc


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

Advertisements
Categories: DBA Stuff Tags:
  1. Saurabh
    February 28, 2012 at 1:39 pm

    Thanks Manoj

    Was confused whether INFORMATION_SCHEMA.ROUTINES contained the information about the Views as well But as per the blog I see that this information is stored in
    INFORMATION_SCHEMA.TABLES / INFORMATION_SCHEMA.VIEWS

    –Saurabh

  1. October 23, 2015 at 5:05 pm
  2. December 13, 2012 at 3:17 pm
  3. December 9, 2012 at 4:22 am
  4. May 16, 2011 at 5:02 am
  5. April 11, 2011 at 8:48 am
  6. March 3, 2011 at 4:45 pm
  7. February 24, 2011 at 8:05 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: