When working with SQL Server databases, it’s not uncommon to come across situations where you need to understand the structure and objects within the database. However, relying on someone else to provide this information can be time-consuming and inefficient. That’s where the INFORMATION_SCHEMA views come in handy.
The INFORMATION_SCHEMA views have been around since SQL Server 7.0 and are still available in the latest version. These views provide a convenient way to query the database schema without the need for complex JOIN statements. They are stored in the MASTER database and can be accessed globally.
One common use case for the INFORMATION_SCHEMA views is performing wildcard searches on various objects in the database. For example, if you’re looking for a table with a column name containing the word “PRE”, you can simply run the following query:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%PRE%'This query will return all the attributes of the INFORMATION_SCHEMA view where the column name matches the wildcard search pattern. The TABLE_NAME column in the result will indicate the table(s) associated with the search.
Similarly, you can use the INFORMATION_SCHEMA views to search for other objects like tables, views, stored procedures, and parameters. Here are a few examples:
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%'By using these queries, you can save a lot of time and effort in digging through the database. No more worrying about mistyping “SELECT” or “INFORMATION” and getting frustrated. To make things even easier, you can create a macro using a stored procedure like dbo.sp_FindFromSchema to eliminate any typographical errors involved in querying the INFORMATION_SCHEMA views.
The dbo.sp_FindFromSchema stored procedure can be used as a macro in SQL Query Analyzer (SQA) for SQL Server 7.0 and SQL Server 2000. Although SQA is not available in SQL Server 2005, you can still add macros in the management console using the “New Query” option. Simply go to “TOOLS”, “CUSTOMIZE”, “KEYBOARD…” (or the equivalent button in SQL Server 2005) and add the stored procedure script as a macro.
With the macro in place, you can easily retrieve INFORMATION_SCHEMA data without having to type the entire query. Just press the assigned keyboard shortcut (e.g., CTRL+3) and the macro will execute. The dbo.sp_FindFromSchema stored procedure takes two parameters: <KeyValue> and <ObjectType>. The ObjectType parameter determines the type of object you want to search for.
Here are some examples of using the dbo.sp_FindFromSchema macro:
sp_FindFromSchema 'cus', 0 -- Find all table names containing "cus"
sp_FindFromSchema 'cus', 1 -- Find all column names containing "cus"
sp_FindFromSchema 'cus', 2 -- Find all stored procedure names containing "cus"
sp_FindFromSchema 'Total=', 3 -- Find all stored procedures containing 'Total=' in the codeBy using the script behind the defined macro, you can easily search for other objects like views, view code, and parameters to stored procedures.
In conclusion, the INFORMATION_SCHEMA views provide a wealth of information about a SQL Server database. Whether you’re a developer, analyst, or database administrator, accessing these views can greatly simplify your schema object searches. So, next time you need to explore the database structure, give the INFORMATION_SCHEMA views a try and save yourself some time and frustration.
Happy coding!