Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Searching Unidentified Tables and Columns for Specific Content : Page 2

You can't let a little thing like not knowing a database's structure keep you from your work. Without identifying tables and columns, you can still find specific values and columns.


advertisement

A SELECT Solution for Finding Column Names

Sometimes you will be familiar with the database's structure, but won't recall a table or column's full name. When this happens, run the following SELECT statement in SQL Server 2000:

SELECT c.Table_Schema, c.Table_name, c.Column_name FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.Table_schema = t.Table_SCHEMA AND c.Table_name = t.Table_name WHERE TABLE_TYPE = 'BASE TABLE' AND c.Column_name LIKE searchstring



The above script will work in SQL Server 2000, 2005, and 2008. The following script is based on the new schema tables for 2005 and 2008, and it guarantees forward compatibility:

SELECT t.name AS TableName, sc.name AS SchemaName, c.name AS ColumnName, types.name AS TypeName, st.name AS TypeSchemaName, t.type AS type FROM sys.all_columns c INNER JOIN sys.all_objects t WITH (NOLOCK) ON c.object_id=t.object_id LEFT JOIN sys.schemas sc WITH (NOLOCK) ON t.schema_id=sc.schema_id LEFT JOIN sys.types types WITH (NOLOCK) ON c.user_type_id=types.user_type_id LEFT JOIN sys.schemas st WITH (NOLOCK) ON st.schema_id=types.schema_id WHERE t.type IN ('U') AND c.Column_name LIKE searchstring ORDER BY c.object_id, c.column_id

Figure 2. Searching SELECT Against Northwind: Run this SELECT statement to find specific columns.

To search a SQL Server database for a table or column whose full name you can't remember, you can run a SELECT statement like this:

SELECT c.Table_Schema, c.Table_name, c.Column_name FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.Table_schema = t.Table_SCHEMA AND c.Table_name = t.Table_name WHERE TABLE_TYPE = 'BASE TABLE' AND c.Column_name LIKE '%order%'

Figure 2 shows the results of running the above statement against Northwind.

As you can see, the SELECT returned several instances of column names that contained the string "order." BASE TABLE refers to user-created tables and not system tables; that's how tables are defined within the information_schema table.

Intuitive Searches When You're Missing Information

Searching known entities is simple. When you don't know the names of tables and columns, a search requires more work. You can use a stored procedure to search unidentified tables for specific values. Or, you can use a SELECT statement to locate columns where the name matches a pattern or keyword. Both solutions are useful tools to add to your arsenal.



Francisco Tapia is a database administrator working for a large manufacturing company. He has been working with SQL Server since version 6.5. You can read more from Tapia at sqlthis.blogspot.com.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap