To retrieve data from system tables use:
Meta-data stored procedures:
Meta-data functions:
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:
EXEC sp_helpdb database_name
Example: Show properties of all files associated with the current database:
EXEC sp_helpfile