Overview

Having been involved with numerous IT projects, there is usually a database involved and one person who knows where "all the bodies are buried". In effect, this person knows how the database is setup, where and what all the database objects (tables, columns, views, stored procedures, etc.) are/for. That person is sometimes too busy to help you in understanding or finding something in the database. Obtaining clarity of the database on your own is not always the easiest thing to do. Enter the INFORMATION_SCHEMA views to rapidly aid you in your quest.

INFORMATION_SCHEMA

The INFORMATION_SCHEMA views have been around since SQL 7.0 of the MSQL DBMS and are available in the latest version. Using the INFORMATION_SCHEMA views is similar to using the SYSOBJECT and other SYS like tables but affords easier querying because there are no JOINS to write. The INFORMATION_SCHEMA views are stored in the MASTER database and are available globally.

Database Schema Object Searches

I have used the INFORMATION_SCHEMA views extensively when performing wild-card searches of various objects in a database. For example, when searching for a particular table whose column name contains "PRE" I would use the following query:

Select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like '%PRE%'

The result would return all attributes of INFORMATION_SCHEMA with the predicate containing a wild card search of '%PRE%'. Of most importance is the column TABLE_NAME. It contains the table(s) with which the wild-card search is associated.

The same holds true for other INFORMATION_SCHEMA views:

Select * from Information_Schema.tables where table_name like '%some value%'
Select * from Information_Schema.views where table_name like '%some value%'
Select * from Information_Schema.views where view_definition like '%some value%'
Select * from Information_Schema.routines where routine_name like '%some value%'
Select * from Information_Schema.routines where routine_definition like '%some value%'
Select * from Information_Schema.parameters where parameter_name like '%some value%'

Unless your typing abilities are error free, these queries will save you a lot of time in digging. If you are me

and often mistype SLECT or INOFRMATOIN instead of SELECT or INFORMATION, I often have to correct my typo's and get frustrated. However, creating a macro using the following script will completely remove typo-graphical errors involved with querying the INFORMATION_SCHEMA views.

dbo.sp_FindFromSchema

dbo.sp_FindFromSchema aids in schema object searches. I use this script as a macro (CTRL+3) and can easily return INFORMATION_SCHEMA data without having to type "Select * from Information_Schema...".

Macros

The following procedure applies to SQL Query Analyzer (SQA) for versions SQL 7.0, SQL 2000. Although SQA does not necessarily exist in SQL 2005, the ability to add macros exists in the management console when using the "New Query" option. To add macros, mouse-click the menu item "TOOLS", "CUSTOMIZE", "KEYBOARD..." (button in SQL2005) and type/paste a stored procedure script of your choice. Further, you can add simple SQL statements as well.

The SQA version before SQL 2005 allowed for dynamic parameters to predefined keyboard macros. For example, from within a query session/connection, highlighting some comma delimited syntax and using CTRL+5. The comma delimited syntax would act as parameter(s) to a stored procedure defined at keyboard macro CTRL+5. Without a macro, typing My_StoredProcedureName 1,0,4,'T' and highlighting and executing it (F5) would work too but I would no doubt misspell, mistype, and get frustrated. For parameter-less macro functions, using SQL 2005 query sessions will not pose any issues. Much to my extreme disappointment, I have had little success in passing parameters into macro defined stored procedures using SQL 2005 Management Studio. Maybe there is an obscure "SET" statement that needs to be turned on or off.

You will of course have to create this script in your local database in order for the macro to work. Or if you are the DBA, add the script to the MASTER database and it will be globally available.

sp_FindFromSchema Usage

<KeyValue>, <ObjectType>
ObjectType
0 - Table name
1 - Column name
2 - Stored procedure, trigger, user defined function name
3 - Stored procedure, triger code pattern
4 - View name
5 - View code pattern
6 - Parameter name

Example without using a macro:

sp_FindFromSchema cus ,0

Example using a Macro

Once the stored procedure script has been created, press CTRL+3 on any blank area of your SQA workspace. The following should be returned:

Invalid parameters supplied!
============================
Title: dbo.sp_FindFromSchema
Purpose: Aids in table, column, sproc, view searches.
Note: Use this sproc in a keyboard shortcut of SQL Query Analyzer
Usage:
<KeyValue>, <ObjectType>
ObjectType
 0 - Table name
 1 - Column name
 2 - Stored procedure, trigger, user defined function name
 3 - Stored procedure, triger code pattern
 4 - View name
 5 - View code pattern
 6 - Parameter name
Example:
sp_FindFromSchema <string pattern>, 0

Using Northwind Database

Table Names
I want to find all table names that have "cus" in the name;

cus,0 -- highlight and press CTRL+3

Column Names
I want to find all column names that have "cus" in the name;

cus,1 -- highlight and press CTRL+3

Stored Procedure Names
I want to find all stored procedure names that have "cus" in the name;

cus,2 -- highlight and press CTRL+3

Stored Procedure Content
I want to find all stored procedures that have 'Total=' contained within

'Total=',3 -- highlight and press CTRL+3 (single ticks enclose the pattern)

Using the script behind the defined macro, searching for other objects such as views, view code, and parameters to stored procedures is easily accomplished.

Conclusion

The INFORMATION_SCHEMA views contain all the information about a particular database. Accessing the views using "SELECT * FROM INFORMATION_SCHEMA" or a macro will, in rapid time, aid schema object searches. The inspiration behind dbo.sp_FindFromSchema came from an article that I read on SQLServerCentral.com involving a work around to querying tables in SQA without having to type "SELECT * FROM". You will note my CTRL+4 macro that calls sp_SelectFromTable. This is a similar script to dbo.sp_FindFromSchema but is for another article.

I am a hands on typist when creating queries as it affords me a direct grasp as to what is happening, for example complex join creation - rather than using a tool (call me old-school). During the testing of several of my macro procedures in the SQL 2005 Management Studio, it was clear that parameters simply cannot get passed dynamically by highlighting delimited text and executing. Changing the procedure name to something very short will work but it would not be a macro. I would glady be open for suggestions.

Happy Coding!