T-SQL script to find column in database

Many a times we need to find all tables having a particular column in a database. Here is a T-SQL script to find column in database.

SELECT 
	DB_NAME(DB_ID()) As DatabaseName,
	OBJECT_SCHEMA_NAME(objects.object_id,db_id()) AS SchemaName,
	objects.name As TableName,
	columns.name As ColumnName,
	types.name
FROM sys.objects objects
JOIN sys.columns columns
ON objects.object_id=columns.object_id
JOIN sys.types types ON 
types.user_type_id=columns.user_type_id
AND columns.name='AddressID'

The above query uses sys.objects  , sys.columns and sys.types to find all tables having a column “AddressID”. The output from the above query is given below.

The other way is to use the Information_Schema view as shown below

SELECT TABLE_SCHEMA, TABLE_NAME,COLUMN_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME='AddressID'

The output from the above query is shown below.

 
Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook