|
The INFORMATION_SCHEMA
views are SQL-92 ANSI standard conforming views with database metadata, data which describes the database itself. The views are the same in SQL Server, Sybase, ORACLE and IBM DB2.
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
-- Find tables with column name OrderDate
SELECT TABLE_SCHEMA,
TABLE_NAME
FROM [AdventureWorks2008].[INFORMATION_SCHEMA].[TABLES] T
WHERE T.TABLE_TYPE = 'BASE TABLE'
AND 'OrderDate' IN (SELECT COLUMN_NAME
FROM
[AdventureWorks2008].[INFORMATION_SCHEMA].[COLUMNS] A
WHERE A.TABLE_SCHEMA = T.TABLE_SCHEMA
AND A.TABLE_NAME = T.TABLE_NAME)
ORDER BY TABLE_SCHEMA,
TABLE_NAME
/* TABLE_SCHEMA TABLE_NAME
Purchasing PurchaseOrderHeader
Sales SalesOrderHeader */
-------------
-- 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 ------------
-- sys ALL views - specific to SQL Server
SELECT TOP 10 * FROM [AdventureWorks2008].[sys].[all_objects]
SELECT TOP 10 * FROM [AdventureWorks2008].[sys].[all_columns]
SELECT TOP 10 * FROM [AdventureWorks2008].[sys].[all_sql_modules]
SELECT TOP 10 * FROM [AdventureWorks2008].[sys].[all_parameters]
SELECT TOP 10 * FROM [AdventureWorks2008].[sys].[all_views]
----------
Related link: http://sqlusa.com/bestpractices/dynamicsql/
|
