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
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 0
SELECT 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
USE 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
information_schema.column_privileges
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
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
Lists databases in an instance.
USE 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
sys.databases
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
Other database information views: sys.database_files, sys.database_mirroring, sys.database_recovery_status, sys.master_files
Windows-level principals
SQL Server-level principals
Database-level principals
