Sunday, September 26, 2010

Using INFORMATION_SCHEMA in SQL Server

The INFORMATION_SCHEMA in SQL Server is a powerful view to access metadata about the database. It is powerful and can help do quick analysis on metadata, column search, etc.

For example:
1. How about a data dictionary of all fields in your database? Can't remember the name of the field you added in the CUSTOMER entity? Just go to the information schema.

SELECT * from INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Your Table'



2. If you want to Find all the places where URL are stored in the database and given that you stick to a convention such as add "URL" as suffix for all fields containing URL, you can run the following query and get quick results

SELECT * from INFORMATION_SCHEMA.COLUMNS
WHERE column_name like '%URL'

No comments:

Post a Comment