Hi there,
Is there a quick way to list all the tables in a DB that contain a certain column name?
Thanks
Sselect name from sysobjects o
where exists (select 1 from information_schema.columns c
where o.name = c.table_name
and column_name like '%column_name_patter%')|||Thanks for this!!!!!|||Thanks again!
I've now got a list of all the tables containing a certain column name, but I'm trying to expand this further to narrow down the results to:
all tables containing columnX and where columnX >= 10000 and <= 20000
Any ideas how I can search on this criteria?
Thanks I'd appreciate any input or advice.
S|||I'll give you what you asked for... You'll have to decide if that is what you want (I suspect that it is not).SELECT o.name
FROM dbo.syscolumns AS c
INNER JOIN dbo.sysobjects AS o
ON (o.id = c.id)
WHERE c.name = 'whatever'
AND c.name BETWEEN '10000' AND '20000'What I suspect that you really want is to check the values of the column in the tables themselves to see if there are rows in the table with column values between 10000 and 20000. That is a slightly more challenging request.
-PatP|||select 'select * from ' + name + ' where ' + column_name + ' between 10000 and 20000' from sysobjects o inner join information_schema.columns c
on o.name = c.table_name
where column_name like '%column_name_patter%'
Then you can execute the resulting series of SELECT statements. Or you can always put it into a cursor and do "EXEC ('...statement...)" on each row.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment