datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to use the INFORMATION_SCHEMA views?

The INFORMATION_SCHEMA views are SQL-92 ANSI standard conforming views with database metadata.

You can use regular 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

 

-- Using information schema views meta data

USE AdventureWorks;

GO

-- List all tables

SELECT DatabaseName = TABLE_CATALOG,

       SchemaName = TABLE_SCHEMA,

       TableName = TABLE_NAME,

       TableOrView = TABLE_TYPE

FROM     INFORMATION_SCHEMA.TABLES

ORDER BY TableOrView

GO

/* Partial results

 

DatabaseName      SchemaName  TableName               TableOrView

AdventureWorks    Purchasing  Vendor                  BASE TABLE

AdventureWorks    Purchasing  PurchaseOrderDetail     BASE TABLE

AdventureWorks    Person      Contact                 BASE TABLE

*/
------------

 

-- List all views with table usage

SELECT *

FROM     INFORMATION_SCHEMA.VIEW_TABLE_USAGE

ORDER BY TABLE_SCHEMA,

         TABLE_NAME

/* Partial results

 

VIEW_CATALOG      VIEW_SCHEMA       VIEW_NAME                     TABLE_CATALOG      TABLE_SCHEMA      TABLE_NAME

AdventureWorks    HumanResources    vEmployeeDepartment           AdventureWorks      HumanResources    Department

AdventureWorks    HumanResources    vEmployeeDepartmentHistory    AdventureWorks      HumanResources    Department

*/
------------

 

-- List all tables/views with columns

SELECT *

FROM     INFORMATION_SCHEMA.COLUMNS

ORDER BY TABLE_SCHEMA,

         TABLE_NAME,

         ORDINAL_POSITION

/* Partial results

TABLE_CATALOG     TABLE_SCHEMA      TABLE_NAME                          COLUMN_NAME      ORDINAL_POSITION

AdventureWorks    Production        vProductModelCatalogDescription     Maintenan…  10

AdventureWorks    Production        vProductModelCatalogDescription     Wheel       11

AdventureWorks    Production        vProductModelCatalogDescription     Saddle      12

AdventureWorks    Production        vProductModelCatalogDescription     Pedal       13

AdventureWorks    Production        vProductModelCatalogDescription     BikeFrame   14

*/
------------

 

-- All stored procedures and user-defined functions with definition

SELECT SchemaName = Routine_Schema,

       SprocName = Routine_Name,

       SprocDefinition = Routine_Definition

FROM     INFORMATION_SCHEMA.ROUTINES

ORDER BY SchemaName,

         SprocName

 

GO

 

/* Partial results

 

SchemaName  SprocName                           SprocDefinition

dbo         ufnGetProductDealerPrice            CREATE FUNCTION ...

dbo         ufnGetProductListPrice              CREATE FUNCTION ...

dbo         ufnGetProductStandardCost           CREATE FUNCTION ...

*/
------------

 

-- SQL table select iff (if and only if ) table exists

-- SQL information schema views

USE AdventureWorks;

IF EXISTS (SELECT 'FOUND'

           FROM   INFORMATION_SCHEMA.TABLES

           WHERE  table_schema = 'Production'

                  AND table_name = 'Product')

  SELECT   TOP 3 ProductName = Name,

                 ListPrice

  FROM     Production.Product

  ORDER BY Newid()

 

GO

/* Results

 

ProductName                   ListPrice

Mountain-300 Black, 40        1079.99

Lock Nut 9                    0.00

Road-250 Black, 52            2443.35

*/
------------

-- T-SQL using INFORMATION_SCHEMA views metadata in dynamic SQL
USE
AdventureWorks2008;

DECLARE @DynamicSQL nvarchar(max) = '', @Schema sysname, @Table sysname;

SELECT @DynamicSQL = @DynamicSQL + 'SELECT '''+QUOTENAME(TABLE_SCHEMA)+'.'+

  QUOTENAME(TABLE_NAME)+''''+

  '= COUNT(*) FROM '+ QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME) +';'

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

ORDER BY TABLE_SCHEMA, TABLE_NAME

PRINT @DynamicSQL -- test & debug

  /* .... ;SELECT '[Production].[ProductDescription]'= COUNT(*)

           FROM [Production].[ProductDescription]; ....   */

EXEC sp_executesql @DynamicSQL  -- sql server exec dynamic sql

------------

Related link: http://sqlusa.com/bestpractices/dynamicsql/

 

Exam Prep 70-461
Exam 70-461