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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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