June 8, 2017 at 10:43 pm
I currently have a query that is pulling a list of tables from sysobjects based on a where clause. From these tables, I need to only output the ones that contain a certain value for one column in particular. I tried keeping my original query as is and looping through each table name to find the ones that have this value, but I am looking for something more efficient. Ideally, if there is a way to modify my original query to keep the where clause and check the values in each table as well. Or as an alternative, delete the table names without the value I am looking for, after I pull the original list, without having to loop through each one individually. What is the quickest method to accomplish this?
My current query looks something like this:
SELECT Name
FROM SysObjects
WHERE <condition 1>
AND <condition 2>
ETC.
Thank you.
June 9, 2017 at 8:13 am
A loop is just as efficient, or close enough to it, for doing what you want here as any other method would be.
I suppose you avoid a loop by generating one long string of SQL that tested every table name found in order for the value, but it won't really be any more efficient overall.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 9, 2017 at 10:12 am
Something like this perhaps...
DECLARE
@ColumnName sysname = 'Referral_ID',
@Value VARCHAR(10) = 12345,
@SQL VARCHAR(MAX) = '';
SELECT
@SQL = CONCAT(@SQL, 'SELECT COUNT(*) FROM ', s.name, '.', o.name, ' x WHERE x.', @ColumnName, ' = ', @Value, ';', CHAR(13), CHAR(10))
FROM
sys.objects o
JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE
o.type = 'U'
AND EXISTS (
SELECT 1
FROM
sys.columns c
WHERE
o.object_id = c.object_id
AND c.name = @ColumnName
);
PRINT (@SQL);
--EXEC sys.sp_executesql @SQL;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply