SQL Query to get a list of all tables present in a database which has a particular column.

Requirement:
Sometimes, we need to modify column values in a table. But it is very much possible that a column is being used by other tables in the database. In such cases we need to ensure that our change in one table does not impact the others.
For example, we might need to rename a particular column (say, Empid) in a table. But if that column is being used in other tables, it will have some impact.

Query:
USE Mydb
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name,
t.name AS table_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name='Empid'
ORDER BY schema_name, table_name

Above query will return the list of all tables in Mydb database which contains the column Empid. We can use LIKE operator in place of = if we need column names which are similar to that of Empid.
WHERE c.name LIKE '%Empid%'

Query here will list all stored procedures which use the given column name.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s