How can I make my queries below dynamic? I want to get the Max() of sfilename of all tables in my db. Not all tables in my db have a sfilename column. If they don't then that table can be skipped.
Select Max(Sfilename) from mydb.dbo.HeaderTbl where sfilename like '202%' Union ALL
Select Max(Sfilename) from mydb.dbo.DetailTbl where sfilename like '202%' Union ALL
Select Max(Sfilename) from mydb.dbo.ContactsTbl where sfilename like '202%' Union ALL
Select Max(Sfilename) from mydb.dbo.ItemsTbl where sfilename like '202%'
April 5, 2024 at 1:47 pm
DECLARE @TableString AS nvarchar(MAX)
DECLARE @SqlString as nvarchar(MAX) = ''
DECLARE MyCursor cursor
FOR SELECT concat_ws('.',c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME) x
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.COLUMN_NAME = 'sfilename'
;
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @TableString
WHILE @@FETCH_STATUS = 0 BEGIN
SET @SqlString += 'Select Max(Sfilename) from ' + @TableString + ' where sfilename like ''202%'''
FETCH NEXT FROM MyCursor INTO @TableString
IF @@FETCH_STATUS = 0
SET @SqlString += ' UNION ALL ' + CHAR(13) + CHAR(10)
ELSE
SET @SqlString += ';'
END
CLOSE MyCursor
DEALLOCATE MyCursor
PRINT @SqlString
EXEC(@SqlString)
April 5, 2024 at 1:48 pm
DECLARE @sql VARCHAR(MAX);
SELECT @sql
= STRING_AGG (
CONCAT (
'SELECT Max(Sfilename) from mydb.'
,SCHEMA_NAME (c.column_id)
,'.'
,t.name
,' where sfilename like ''202%'''
)
,' UNION ALL '
)
FROM sys.columns c
JOIN sys.tables t
ON t.object_id = c.object_id
WHERE c.name = 'sfilename';
PRINT @sql;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 5, 2024 at 2:05 pm
I think I will also need to add the table name to the result of the query.
April 5, 2024 at 2:27 pm
String_AGG will not work for me. I just realized, I'm working on SQL 2016.
April 5, 2024 at 2:32 pm
I'm also getting a'Concat_ws' is not a recognized built-in function name.
April 5, 2024 at 2:34 pm
I think I will also need to add the table name to the result of the query.
DECLARE @TableString AS nvarchar(MAX)
DECLARE @SqlString as nvarchar(MAX) = ''
DECLARE MyCursor cursor
FOR SELECT concat_ws('.',c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME) x
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.COLUMN_NAME = 'sfilename'
;
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @TableString
WHILE @@FETCH_STATUS = 0 BEGIN
SET @SqlString += 'Select ''' + @TableString + ''' TableName, Max(sfilename) max_sfilename from ' + @TableString + ' where sfilename like ''202%'''
FETCH NEXT FROM MyCursor INTO @TableString
IF @@FETCH_STATUS = 0
SET @SqlString += ' UNION ALL ' + CHAR(13) + CHAR(10)
ELSE
SET @SqlString += ';'
END
CLOSE MyCursor
DEALLOCATE MyCursor
PRINT @SqlString
EXEC(@SqlString)
I would make it truly more generic (might as well). Definitely avoid the use of INFORMATION_SCHEMA views, since they are not 100% reliable and often seem very slow. I, too, would use the "dreaded" cursor here, since it gives better control of things, and the number of rows involved should be limited. I can write a version avoiding a cursor and using XML concat if you prefer:
DECLARE @column_name nvarchar(128);
DECLARE @column_value varchar(10);
DECLARE @display_sql bit;
DECLARE @exec_sql bit;
DECLARE @schema_name nvarchar(128);
DECLARE @table_name nvarchar(128);
DECLARE @table_name_pattern nvarchar(128);
DECLARE @sql nvarchar(max);
DECLARE @sql_template nvarchar(max);
SET @exec_sql = 0;
SET @display_sql = 1;
SET @column_name = 'Sfilename';
SET @column_value = '202%';
SET @table_name_pattern = '%';
SET @sql_template = N'SELECT MAX([$column$]) AS [$column$] FROM [$schema$].[$table$] WHERE [$column$] LIKE ''$column_value$'''
--**********************************************************************************************************************
IF @exec_sql = 0
SET @display_sql = 1;
DECLARE column_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT t.name AS table_name, SCHEMA_NAME(t.schema_id) AS schema_name, c.name AS column_name
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
WHERE c.name = @column_name AND t.name LIKE @table_name_pattern
ORDER BY schema_name, t.name
OPEN column_cursor;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM column_cursor INTO @table_name, @schema_name, @column_name;
IF @@FETCH_STATUS <> 0
BREAK;
SET @sql = ISNULL(@sql + NCHAR(13) + NCHAR(10) + N'UNION ALL' + NCHAR(13) + NCHAR(10), N'') +
REPLACE(REPLACE(REPLACE(REPLACE(@sql_template,
N'$column$', @column_name),
N'$column_value$', @column_value),
N'$schema$', @schema_name),
N'$table$', @table_name)
END /*WHILE*/
CLOSE column_cursor;
DEALLOCATE column_cursor;
IF @display_sql = 1
SELECT @sql AS [--sql]
IF @exec_sql = 1
EXEC(@sql);
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 5, 2024 at 4:31 pm
Scott,
How do I add the tablename to this query? So it displays the tablename with the result of the max().
April 5, 2024 at 5:00 pm
Scott,
How do I add the tablename to this query? So it displays the tablename with the result of the max().
SET @sql_template = N'SELECT MAX([$column$]) AS [$column$], ''[$schema$].[$table$]'' AS [Table Name] FROM [$schema$].[$table$]WHERE [$column$] LIKE ''$column_value$'''
April 5, 2024 at 5:56 pm
I would make it truly more generic (might as well). Definitely avoid the use of INFORMATION_SCHEMA views, since they are not 100% reliable and often seem very slow. I, too, would use the "dreaded" cursor here, since it gives better control of things, and the number of rows involved should be limited. I can write a version avoiding a cursor and using XML concat if you prefer:
while XML Concat would work for most cases, if number of db's + tables is too big it can slow down considerably the parsing and execution of the query - I've had that issue when trying to drop thousands of tables on a db (can reach 300k tables per execution to be dropped... darn third party software)
had to resort to processing a smaller number of drops in a loop until all done
April 5, 2024 at 6:00 pm
GrassHopper wrote:Scott, How do I add the tablename to this query? So it displays the tablename with the result of the max().
SET @sql_template = N'SELECT MAX([$column$]) AS [$column$], ''[$schema$].[$table$]'' AS [Table Name] FROM [$schema$].[$table$]WHERE [$column$] LIKE ''$column_value$'''
Yep, although technically you don't need the brackets there since the value is enclosed in quotes. Naturally I use the brackets otherwise just in case the column name or table name (or, far less likely, the schema name) contains a special character.
Personally I find it easier to change a template rather than changing code where the string is built in-line. You basically have to go thru all the construction code to make changes to it, and it's, to me, much more complex to do.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply