How to query for columns in sql database

Question

I am merging a few tables and I need to query for specific column names. Reason being these tables I am condensing have FK candidate columns but no FK so I would have to keep looking through every table to see if it contained a specific column name and that seems to be wearing on the eyes a little.

I got this from another post but it is not complete.

SELECT 'SELECT ''' + TABLE_NAME + '.' + column_name + 
   ''' FROM ' + TABLE_NAME + ' WHERE ' + 
   column_name + ' = ''[THE COLUMN NAME I AM LOOKING FOR]'''
FROM INFORMATION_SCHEMA.COLUMNS

The TSQL above seems to not be structured correctly or I am missing something.

do you know the table and column names or are you creating something dynamic? — altschuler
– altschuler, Commented Aug 4, 2012 at 18:51
Do you realize that this creates dynamic SQL that needs to be run separately? And, that you need to replace "[THE COLUMN NAME I AM LOOKING FOR]" with a column name? — Gordon Linoff
– Gordon Linoff, Commented Aug 4, 2012 at 18:52

Yuck · Accepted Answer · 2012-08-04 19:07:07Z

2

This query will get a list of columns (along with their schema and table names) matching whatever you put into the parameter @SomeColumnName:

DECLARE @SomeColumnName VarChar(200) = 'Test';

SELECT S.name SchemaName, T.name TableName, C.name ColumnName
FROM sys.columns C
     JOIN sys.tables T ON C.object_id = T.object_id
     JOIN sys.schemas S ON T.schema_id = S.schema_id
WHERE C.name = @SomeColumnName;

And if you are using a version of SQL Server that doesn't have the newer DMVs, use this:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.COLUMN_NAME = @SomeColumnName;

answered Aug 4, 2012 at 19:07

Yuck

51.1k13 gold badges108 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query for columns in sql database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related