SQL Syntax return TABLE NAMES by column VALUE

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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Syntax return TABLE NAMES by column VALUE

Author

Topic

nferri
Starting Member

5 Posts

Posted - 2012-11-21 : 12:40:35

Hi, I have a database where every table has an id column (CID) once this value changes all the tables must be checked and updated with the new CID value. I would like a write a query that returns all table names that DO or DO NOT have "CID = x" where x is the current CID value I define through a parameter. This way i can check and see what tables still need to be updated. Please help. Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 12:49:51

[code]-- if 9455 is the CID you want to test for
CREATE TABLE #tmp(tablename VARCHAR(255), UnmatchedCID VARCHAR(32));
INSERT INTO #tmp
EXEC sp_msforeachtable
'select top 1 ''?'' as tablename, ''YES'' UnmatchedCID from ? where CID <> 9455'
INSERT INTO #tmp
EXEC sp_msforeachtable
'select top 1 ''?'' as tablename, ''NO'' UnmatchedCID from ? where CID = 9455'
SELECT * FROM #tmp
DROP TABLE #tmp;
[/code]