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.

1_t sql script to find column in database

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.

2_T sql script to find column in database

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *