CODEWITHSUNDEEP
sql-server
Theman
Theman

Reputation: 231

A query to list the names of all tables that contain a column with a specific name

I have a database called Database1 that contains a large number of tables. I am trying to identify all the tables that have a column called Order Number, but going through each table manually will take a long time.

Is it possible to execute a query that returns all names of tables that have a column with a specific name?

I tried this, but it didn't recognise Database1, nor column:

SELECT TOP 100 * FROM Database1 WHERE column = 'Order Number'

Upvotes: 1

Views: 154

Answers (2)

msi77
msi77

Reputation: 1632

    use Database1
    SELECT table_name FROM INFORMATION_SCHEMA.columns 
       WHERE column_name = 'Order Number'

Upvotes: 0

mehdi lotfi
mehdi lotfi

Reputation: 11601

Using the following query you can get list of tables that have column named 'Order Number':

SELECT Schema_name(t.schema_id), 
       Object_name(t.object_id) 
FROM   sys.columns c 
       INNER JOIN sys.tables t 
               ON t.object_id = c.object_id 
WHERE  c.name = 'Order Number'

and by using following query you can get records of above table:

DECLARE @ReturnValue NVARCHAR(max) = '' 

SELECT @ReturnValue = @ReturnValue + 'SELECT TOP(100) * FROM [' 
                      + Schema_name(t.schema_id) + '].[' 
                      + Object_name(t.object_id) + '] ' + Char(10) 
                      + Char(13) 
FROM   sys.columns c 
       INNER JOIN sys.tables t 
               ON t.object_id = c.object_id 
WHERE  c.name = 'Order Number' 

PRINT @ReturnValue -- Inorder to check your query 

EXEC(@ReturnValue)

Upvotes: 1

Related Questions