DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to select from INFORMATION_SCHEMA views?

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

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE