|
The INFORMATION_SCHEMA views are SQL-92 ANSI standard views for selected database metadata. You can use regular T-SQL SELECT statements to obtain their contents. Here are the views:
Information_Schema.Check_Constraints
Information_Schema.Column_Domain_Usage
Information_Schema.Column_Privileges
Information_Schema.Columns
Information_Schema.Constraint_Column_Usage
Information_Schema.Constraint_Table_Usage
Information_Schema.Domain_Constraints
Information_Schema.Domains
Information_Schema.Key_Column_Usage
Information_Schema.Parameters
Information_Schema.Referential_Constraints
Information_Schema.Routine_Columns
Information_Schema.Routines
Information_Schema.Schemata
Information_Schema.Table_Constraints
Information_Schema.Table_Privileges
Information_Schema.Tables
Information_Schema.Views
Information_Schema.View_Column_Usage
Information_Schema.View_Table_Usage
USE AdventureWorks2008;
GO
-- List all tables
SELECT
DatabaseName=TABLE_CATALOG
, SchemaName = TABLE_SCHEMA
, TableName = TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY SchemaName, TableName
GO
-- List all views
SELECT
DatabaseName=TABLE_CATALOG
, SchemaName = TABLE_SCHEMA
, TableName = TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
ORDER BY SchemaName, TableName
GO
-- List all views with table usage
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
ORDER BY TABLE_SCHEMA, TABLE_NAME
GO
-- List all tables/views with columns
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
/*
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION
.....
AdventureWorks2008 Sales CreditCard CreditCardID 1
AdventureWorks2008 Sales CreditCard CardType 2
AdventureWorks2008 Sales CreditCard CardNumber 3
AdventureWorks2008 Sales CreditCard ExpMonth 4
AdventureWorks2008 Sales CreditCard ExpYear 5
AdventureWorks2008 Sales CreditCard ModifiedDate 6
.....
*/
-- All stored procedures and functions with definition
SELECT
SchemaName = Routine_Schema,
SprocName = Routine_Name,
SprocDefinition = Routine_Definition
FROM INFORMATION_SCHEMA.ROUTINES
ORDER BY SchemaName, SprocName
GO
-- List all schemas
SELECT
DatabaseName=CATALOG_NAME
, SchemaName = SCHEMA_NAME
, SchemaOwner = SCHEMA_OWNER
FROM INFORMATION_SCHEMA.SCHEMATA
ORDER BY SchemaOwner, SchemaName
GO
-- List parameters for stored procedures and functions
SELECT
SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION,
PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.PARAMETERS
ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION
GO
|
