Showing posts with label catalog. Show all posts
Showing posts with label catalog. Show all posts

SQL Server: the data dictionary


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
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 and information_schema.column_privileges
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
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
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, sys.databases
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


sp_statistics

sp_fkeys


sp_pkeys

sp_table_privileges

sp_server_info

sp_tables