SCAN ALL TABLES FOR A FIELD NAME

Question

SCAN ALL TABLES FOR A FIELD NAME

recycled

Mr or Mrs. 500

Points: 579
More actions
December 5, 2003 at 3:49 pm

#160934

I need to update a field in all tables where the field exists. Is there a generic query which I can run which would help me identify which tables contain the field name?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Use the syscolumns table and search by name field. FOr you output do OBJECT_NAME([id]) as the id field is the numeric table reference and object_name is the function that returns the name based on id.

snikpoh SSCommitted Points: 1873 More actions · Answer 2

There are a couple of scripts on the site that do this, try ...

http://www.sqlservercentral.com/scripts/contributions/565.asp

http://www.sqlservercentral.com/scripts/contributions/511.asp

Jonathan SSC-Insane Points: 20427 More actions · Answer 3



SELECT Table_Schema + '.' + Table_Name

FROM INFORMATION_SCHEMA.COLUMNS

WHERE OBJECTPROPERTY(OBJECT_ID(Table_Name),'IsUserTable') = 1

AND Column_Name = 'YourColumnName'

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

http://www.insidesql.de/scripts/sqlserverfaq.php?id=33

When looking at the execution plan of each, Jonathan's solution is efficient.

For the sake of 'completeness' you can also use this



SELECT 

table_schema, table_name 

FROM 

information_schema.columns

WHERE

column_name = 'MsgDateReceived'





SELECT 

s2.name 

FROM 

dbo.syscolumns s1 

JOIN 

dbo.sysobjects s2 

ON 

s1.id = s2.id 

WHERE 

s1.NAME='MsgDateReceived'

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]