|
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/
|
