question

KathyTheGreat avatar image
KathyTheGreat asked

Iterate over system tables to get a list of tables

I need to create a query to first get all the tables with a particular column ( column name is log ) and, once I have this list of tables, iterate over each table to see if the log column contains a specific piece of text. So far I have this: SELECT b.name into mytemp FROM syscolumns a INNER JOIN sysobjects b ON a.id = b.id AND b.type = 'u' WHERE a.name = 'log' If I do a select * from mytemp, I get the 5 tables I'm looking for. I need to write some kind of loop to iterate over these 5 tables, do a select log from thistable where log like '%hack%'. I'm close ( at least I'd like to think so ), but I need the rest of this query. I hope someone can help me. Thanks.
system-tables
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Cyborg avatar image
Cyborg answered
Hello kathy, You can use dynamic sql DECLARE @SQL NVARCHAR(1000), @parameter NVARCHAR(500), @outPut VARCHAR(1000) SET @SQL = 'SELECT @OUT = STUFF((SELECT ''UNION ''+''SELECT ID FROM ''+TABLE_SCHEMA+''.''+TABLE_NAME +'' WHERE Log LIKE ''+''''''%hack%'''''' as ''data()'' FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Log'' FOR XML PATH('''')),1,5,'''')' SET @parameter = N'@out VARCHAR(1000) OUTPUT' exec sp_executesql @SQL,@parameter,@out= @OutPut OUTPUT EXEC (@outPut)
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 use a cursor to iterate and create the dynamic SQL. Also keep in mind that like %somevalue% causes SQL to ignore indexes.
1 Like 1 ·
Hakan winther, i agree with you, If your filter is like 'SomValue%' then optimizer will not ignore the indexes.
0 Likes 0 ·
It's giving me an error: Msg 207, Level 16, State 1, Line 1 Invalid column name 'ID'. This is against sql server 2008.
0 Likes 0 ·
sorry its my fault since i worked with a sample table, replace that id with 'Log'
0 Likes 0 ·
Cyborg - thank you, thank you. It worked great. I so appreciate your help.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.