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