03 December, 2007

Find tables, with data, that have a specific column

We're going to change some Item Numbers in GP. I know, I can use PS Tools to do this but to use that you have to turn off your replication. Long story short, I don't wanna.

Anyway, I had to figure out which tables to update so I wrote a query that would return to me all of the tables, with data, that have an ITEMNMBR column:

select distinct o.Name
from SysColumns c
inner join SysObjects o
on c.id = o.id
inner join SysIndexes i
on c.id = i.id
where c.name = 'ITEMNMBR'
and o.xtype = 'u'
and rowcnt <> 0

I figured that some other SQL Hacks out there might find this useful.

No comments: