To retrieve data from system tables use:

• system stored procedures (e.g. EXEC sp_help Customers)
• system functions (e.g. SELECT USER_NAME(ID))
• information schema views (e.g. SELECT * FROM INFORMATION_SCHEMA.TABLES)
  • INFORMATION_SCHEMA.TABLES
  • INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  • INFORMATION_SCHEMA.CHECK_CONSTRAINTS
  • INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
  • INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
  • INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Meta-data stored procedures:

• sp_help object_name - Provides information on the specified database object
• sp_helpdb database_name - Provides information on the specified database
• sp_helpindex table_name - Provides information on the indexes for the specified table

Meta-data functions:

• COL_LENGTH

Example: Display primary keys and foreign keys:

SELECT CONSTRAINT_CATALOG,
       CONSTRAINT_SCHEMA,
       CONSTRAINT_NAME,
       TABLE_CATALOG,
       TABLE_SCHEMA,
       TABLE_NAME,
       COLUMN_NAME,
       ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Example: Display security information for tables:

SELECT GRANTOR,          -- dbo
       GRANTEE,          -- public, user defined role, user name (eg. lo, taratuta) 
       TABLE_CATALOG,    -- database name
       TABLE_SCHEMA,     -- table owner
       TABLE_NAME,       -- table name (may be the same for each role)
       PRIVILEGE_TYPE,   -- SELECT, INSERT, UPDATE, DELETE
       IS_GRANTABLE      -- YES, NO
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES

Example: Determine if the FirstName column in the Customer table of the Lottery database allows NULL values (returns 0 or 1):

USE Lottery
GO
SELECT COLUMNPROPERTY(OBJECT_ID('Customer'), 'FirstName', 'AllowsNull')

Example: Display user-defined tables:

SELECT LEFT(TABLE_CATALOG, 20) AS TableQualifier,  -- Database name
       LEFT(TABLE_SCHEMA, 20) AS TableOwner,       -- User
       LEFT(TABLE_NAME, 30) AS TableName, 
       TABLE_TYPE AS TableType
FROM INFORMATION_SCHEMA.TABLES

Example: Return the length (in bytes) of a column:

COL_LENGTH('table', 'column')

Example: Return the number of bytes used to represent an expression:

SELECT DATALENGTH(expression)
FROM ORDER
ORDER BY OrderID

Example: Return the current username and the application that the user is using for the current session or connection:

SELECT USER_NAME(), APP_NAME()  -- Result: dbo     SQL Query Analyzer

Example: Return information about all constraints associated with a table:

sp_helpconstraint TABLE_NAME

Example: Return:

• information about the database (owner, dbid, created, status, compatibility_level)
• database size: db_size = initial size of the primary file + initial size of the log file
• information about database files (fileid, filename, filegroup, size, maxsize, growth, usage)

EXEC sp_helpdb database_name

Example: Show properties of all files associated with the current database:

EXEC sp_helpfile