Another Quickie- can Remi the demi(god) help again?!
-
Hi guys,
I've heard there is a sql command which allows you to query a whole db for a certain column name and it will tell you what tables contani that column. Ie if you search for Customer_ID, it will tell you taht Customer_Details, Customer_Address, and so on all contain thiscolumn.
Thanks!
Alex
-
Try:
SELECT Table_Name
FROM Information_Schema.Columns
WHERE Column_Name = 'myCol'
Hope this helps
-
SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.*
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id
WHERE (dbo.sysobjects.xtype = 'u') AND (dbo.syscolumns.name = 'id')
-
In case you didn't notice I was KIDDING about the shrine. All I need as a temple
. -
did it again!
my hero!
-
BTW if you even plan to use SQL SERVER 2005 you're better off with this solution :
SELECT Table_Name
FROM Information_Schema.Columns
WHERE Column_Name = 'myCol'
Since I hear that direct access to the system tables will be forbidden in Yukon... Also there's always the fact that it can be modified without any notice when a patch or a new version comes out.
-
Yes, whenever you can use the INFORMATION_SCHEMA views instead of directly querying the system tables, you should do so.
And yes, SQL Server 2005 might be more restrictive here.
Btw Remi, congrats on crossing to 1,000 posts barrier.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Tx Frank... Still a long way from catching you.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply