Search a table for a column name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Search a table for a column name

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-16 : 10:15:37

I have some very large tables i'm working with (hundreds of columns). Is there a way to search for a column by name in SSMS, just to verify the column exists in the table?

thanks

james

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-16 : 10:16:45

disregard. Found this:
USE AdventureWorks
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_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 LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 10:53:24

i prefer using INFORMATION_SCHEMA.COLUMNS view


SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = <your column name>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-16 : 11:04:50

anytime i can avoid using a join, i will.

thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 11:08:49

quote:
Originally posted by WJHamel

anytime i can avoid using a join, i will.

thanks.

I prefer it for that reason and also that it gives all required names directly without using any other functions like object_name,schema_name etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-16 : 11:24:50

How about something like the above to seek out the column for each table with an IDENTITY property?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 11:26:35

quote:
Originally posted by WJHamel

How about something like the above to seek out the column for each table with an IDENTITY property?

for that i use sys.identity_columns

http://msdn.microsoft.com/en-us/library/ms187334.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 11:32:25

Too bad they don't show the joins in that link

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-16 : 12:27:01

select TABLE_NAME + '.' + COLUMN_NAME, TABLE_NAME

from INFORMATION_SCHEMA.COLUMNS

where TABLE_SCHEMA = 'dbo'

and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =

1

order by TABLE_NAME

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 12:40:33