 |
Distribution of catalog information:
- What is stored in the master database? What is kept in the user databases?
- Base metadata tables are stored in the resource database. Metadata views are available in the master database (system-wide information) and in each user database (db specific and some system-wide information).
- Metadata information can be obtained through catalog views, information schema views, system stored procedures and functions, as well as OLE DB schema rowsets and ODBC catalog funtions.
Obtaining metadata information can be done by using either:
- (a) System Stored Procedures (T-SQL)
- Catalog stored procedures
- (b) System views:
- Object Catalog views
- Dynamic Management Views and Functions
- Information Schema Views
Catalog Stored Procedures and Object Catalog views
 | sp_columns, sys.columns, and information_schema.columns |
USE AdventureWorks2008r2; GO EXEC sp_columns department;Returns 19 columns.. TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME ... TYPE_NAME PRECISION LENGTH -------------------- -------------- --------- ------------ ----------------- ---------- ------ AdventureWorks2008R2 HumanResources Department DepartmentID smallint identity 5 2 AdventureWorks2008R2 HumanResources Department Name Name 50 100 AdventureWorks2008R2 HumanResources Department GroupName Name 50 100 AdventureWorks2008R2 HumanResources Department ModifiedDate datetime 23 16 (or)USE AdventureWorks2008r2; GO EXEC sp_columns department, @column_name= 'name';TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME ... TYPE_NAME PRECISION LENGTH -------------------- -------------- --------- ------------ ----------------- ---------- ------ AdventureWorks2008R2 HumanResources Department Name Name 50 100 (for column information, you may also use:)EXEC sp_help 'HumanResources.department';
sys.columns
Returns a row for each column of an object that has columns (system, internal and user tables, views, table-valued sql function, inline table-valued sql function, table-valued assembly functions).
SELECT name, system_type_id, is_nullable FROM sys.columns WHERE OBJECT_NAME(OBJECT_ID)= 'Department';name system_type_id is_nullable ------------ -------------- ------------ DepartmentID 52 0 Name 231 0 GroupName 231 0 ModifiedDate 61 0SELECT o.name table_name, c.name column_name, t.name data_type, t.length length, t.prec precision FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id LEFT JOIN systypes t ON t.xtype = c.TYPE WHERE o.TYPE = 'U' AND o.name = 'Department' ORDER BY o.name, c.name;table_name column_name data_type length precision ---------- ----------- --------- ------ -------- Department DepartmentID smallint 2 5 Department GroupName NULL NULL NULL Department ModifiedDate datetime 8 23 Department Name NULL NULL NULL
information_schema.columns
Information schema views provide a system table-independent view of the SQL Server metadata.
The idea is that it provides an interface that remains unchanged even after significant changes have been made to the underlying system tables.
USE AdventureWorks2008R2; GO SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Department';TABLE_NAME COLUMN_NAME COLUMN_ID ---------- ----------- -------- Department DepartmentID 1 Department Name 2 Department GroupName 3 Department ModifiedDate 4
| sp_column_privileges and information_schema.column_privileges |
information_schema.column_privilegesUSE AdventureWorks2008R2; GO EXEC sp_column_privileges 'Employee', @table_owner='HumanResources', @column_name = 'SalariedFlag';TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE -------------------- -------------- ---------- ------------ ------- ------- --------- --------- AdventureWorks2008R2 HumanResources Employee SalariedFlag dbo dbo INSERT YES AdventureWorks2008R2 HumanResources Employee SalariedFlag dbo dbo REFERENCES YES AdventureWorks2008R2 HumanResources Employee SalariedFlag dbo dbo SELECT YES AdventureWorks2008R2 HumanResources Employee SalariedFlag dbo dbo UPDATE YES
SELECT table_name, column_name,column_name, privilege_type, is_grantable FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_NAME = 'Department';
| sp_special_columns |
Returns the optimal set of columns that uniquely identify a row in the table.
Returns columns automatically updated when any value in the row is updated.
USE AdventureWorks2008R2; GO EXEC sp_special_columns 'Department', @table_owner='HumanResources';SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE PSEUDO_COLUMN ----- -------------- --------- ----------------- --------- ------ ----- ------------ 1 DepartmentID 5 smallint identity 5 2 0 1
| sp_stored_procedures, sp_sproc_columns, sys.procedures and information_schema.routines |
USE AdventureWorks2008R2; GO EXEC sp_stored_procedures;PROCEDURE_QUALIFIER PROCEDURE_OWNER PROCEDURE_NAME ... ------------------- -------------- ----------------- AdventureWorks2008R2 dbo ufnGetAccountingEndDate;0 ... AdventureWorks2008R2 HumanResources uspUpdateEmployeeHireInfo;1 .. AdventureWorks2008R2 sys sp_spaceused;1 (also)USE AdventureWorks2008R2; GO EXECUTE sp_stored_procedures @sp_owner = N'dbo';
sp_sproc_columns
Returns column information for a stored procedure or user-defined function.
USE AdventureWorks2008R2; GO EXEC sp_sproc_columns @procedure_name = 'uspLogError';PROCEDURE_QUALIFIER PROCEDURE_OWNER PROCEDURE_NAME COLUMN_NAME ... TYPE_NAME ... IS_NULLABLE ------------------- --------------- -------------- ----------- --------- ----------- AdventureWorks2008R2 dbo uspLogError;1 @RETURN_VALUE int NO AdventureWorks2008R2 dbo uspLogError;1 @ErrorLogID int YES
sys.procedures
Returning all stored procedures in a given database;
USE AdventureWorks2008R2; GO SELECT name procedure_name, SCHEMA_NAME(schema_id) AS schema_name, type_desc, create_date, modify_date FROM sys.procedures;
information_schema.routines
Returns the stored procedures and functions that can be accessed by the current user in the current database.
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_BODY, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.routines;
| sp_databases, sys.databases |
Lists databases in an instance.
sys.databasesUSE MASTER; GO EXEC sp_databases;DATABASE_NAME DATABASE_SIZE REMARKS -------------------- ------------ -------- AdventureWorks2008R2 186240 NULL master 5120 NULL model 1792 NULL msdb 20288 NULL MyDB 10240 NULL tempdb 8704 NULL
Other database information views:USE MASTER; GO SELECT name, create_date, compatibility_level, user_access_desc, state_desc, recovery_model_desc FROM sys.databases;name create_date compat_level user_access_desc state_desc rec_model_desc ------------------- ----------------------- ------------ ---------------- ---------- -------------- master 2003-04-08 09:13:36.390 100 MULTI_USER ONLINE SIMPLE ... model 2003-04-08 09:13:36.390 100 MULTI_USER ONLINE FULL TESTSQL 2009-06-08 10:43:13.347 100 MULTI_USER ONLINE BULK_LOGGED AdventureWorks2008R2 2009-06-05 17:40:24.250 100 MULTI_USER ONLINE SIMPLE
sys.database_files, sys.database_mirroring, sys.database_recovery_status, sys.master_files | sp_statistics |
| sp_fkeys |
| sp_pkeys |
| sp_table_privileges |
| sp_server_info |
| sp_tables |
