Speed up Development on Backend and Middle Tier with SQL Scripts
page 4 of 6
by Ameet Phadnis
Feedback
Average Rating: 
Views (Total / Last 10 Days): 28520/ 92

Accessing Meta Data - Information Schema Views

All the above ways do allow us to get the data about data. But the recommended way is Information Schema views. The reasons are next versions of System tables might change but the information schemas would remain the same. It also hides the complexity. If you look back at the System Tables section you will realize that you need to do some research or need to have in depth knowledge of System Tables. But with information schema views it hides all the join information etc. In this section, I will explain common used views.

 

Information_schema.Tables: Returns information about Tables. To return all user defined table names in the database you can write select as –

 

SELECT TABLE_NAME from Information_schema.Tables WHERE TABLE_TYPE = 'BASE TABLE'

 

Information_schema.Columns: Returns information about columns in the table. To get column name, data types and sizes for Users table, you can write SQL as –

 

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'Users'

 

For data types that have fixed size like int, datetime, the CHARACTER_MAXIMUM_LENGTH will return null.

 

Information_schema.TABLE_CONSTRAINTS: Returns information on all constraints. Users can get information on specific tables. The Constraints are identified by the CONSTRAINT_TYPE column.

 

For example, to get all constraints on Users Table you can write SQL as –

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Where Table_name = 'Users'

 

To get Primary key Constraint on the Users table you can write SQL as –

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Where Table_name = 'Users' AND CONSTRAINT_TYPE = 'PRIMARY KEY'

 

Information_Schema.CONSTRAINT_COLUMN_USAGE: Returns column information and the constraint associated with it.

 

For example, in the above example we got PRIMARY KEY CONSTRAINT Information on Users table but we would like to have the column name. The SQL will look like –

 

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = 'PK_USERS'

 

Combining Information_schema.Table_constraints and Information_schema. CONSTRAINT_COLUMN_USAGE, SQL will look like

 

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON            INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME

            WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = 'Users' AND CONSTRAINT_TYPE = 'PRIMARY KEY'

 

Information_schema.REFERENTIAL_CONSTRAINTS: Returns information about foreign key constraints.

 

For example, consider you have two tables in your database. One is users table and one is UserRoles table. Users table has UserID which is referenced in UserRoles table. To get the foreign key column information your SQL will look like –

 

 

SELECT        fkey.Table_name, fkey.Column_name

FROM             INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rkey JOIN

            Information_schema.REFERENTIAL_CONSTRAINTS Ref on rkey.CONSTRAINT_NAME = Ref.Unique_Constraint_Name

            JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fkey ON Ref.CONSTRAINT_NAME = fkey.CONSTRAINT_NAME

WHERE         rkey.Table_Name = 'Users'

 

The above SQL will get you the table name and column names which reference the UserID table in the Users table.

 

Information_Schema.ROUTINES: Returns information on Stored Procedure and functions.

 

To get information on all Stored Procedures in your database your SQL will be –

 

SELECT * FROM Information_Schema.ROUTINES Where Routine_type = 'PROCEDURE'

 

Information_Schema.PARAMETERS: Returns information on Parameters for stored procedure.

 

To get information on Parameters for AddUser stored procedure your SQL will be –

 

SELECT PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM Information_Schema.PARAMETERS Where Specific_name = 'AddUser'

 

This section explained how you can use some of the information schema views to extract data about your database.

 


View Entire Article

User Comments

Title: MetaData   
Name: Sanjay Modi
Date: 2007-04-10 2:25:25 PM
Comment:
its realy helpful my best wishes to user

Sanjay Modi
modi_sanjay@yahoo.com
Title: ing   
Name: Jorgen Pedersen
Date: 2005-09-05 12:10:14 PM
Comment:
Hi. I like your article but I need to find the name of the columns constraint name for the DEFAULT value of the column. Can you help?
Yjanks in advance
Title: Thank you   
Name: Mab
Date: 2005-06-21 8:37:32 AM
Comment:
HI
Its really a very gud articles for the developers. Thanks for sharing such a nice article.

Cheers






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-21 5:52:39 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search