Tuesday, February 28, 2006

Find All tables That Contain A Certain Column

This question was posted today on the tek-tips site and also in the microsoft.public.sqlserver.programming newsgroup
The question was how to find all tables that contain a certain column
For example return all tables that have the column OrderID in the Northwind database
You can get all that information from the INFORMATION_SCHEMA.COLUMNS system view, however that view returns tables as well as views
You have to join with INFORMATION_SCHEMA.TABLES and that view contains a column named TABLE_TYPE that you can use to filter on the type (BASE TABLE or VIEW)
I have only selected a couple of columns from the views, use * to see them all

USE Northwind
GO

SELECT c.TABLE_NAME,
TABLE_TYPE,
COLUMN_NAME,
ORDINAL_POSITION,
IS_NULLABLE,
DATA_TYPE,
NUMERIC_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
WHERE COLUMN_NAME ='orderid'
ORDER BY TABLE_TYPE ,c.TABLE_NAME

1 comment:

zuezuu said...

* Allowing Large Tables to Take Default Storage Para...
* Inserted Value Too Large for Column
* Not Enough Values
* Missing Expression
* SQL Command Not Properly Ended
* Column Ambiguously Defined
* Missing Comma
* Missing Right Parenthesis
* Missing Left Parenthesis
* Missing Keyword
* Invalid Column Name
* Group Function Is Not Allowed Here
* FROM Keyword Not Specified
* Invalid Username or Password
* Common SQL Mistakes/Errors and Resolutions

my-sql-server.blogspot.com