Probably few know that SQL Server makes available a set of undocumented stored procedures that can be useful in certain scenarios that don't involve code that will deployed into production. One of them is sp_MSForEachTable on which I wrote several posts until now. The stored procedure executes one or more scripts based on a set of constraints against the subset of available tables. The first surprising fact is that Copilot does know the respective stored procedure and seems to correctly generate code for it!
Disclaimer: The below code was generated by Copilot via prompts used for demonstration purposes!
Please not that the following code is provided as it is and you can use it on your own risk! Don't run the code in a productive environment and make sure that you have a usable backup! Please consider all caution when you use a feature marked as undocumented!
Let's start with a simple scripts and increase the complexity of the queries as it goes!
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure in SQL
Server
Output:
-- query that uses this stored procedure to update a specific column in all tables: EXEC sp_MSForEachTable 'UPDATE ? SET YourColumn = ''NewValue'' WHERE YourCondition' /* ? is a placeholder for the table name. YourColumn is the column you want to update. NewValue is the new value you want to set. YourCondition is the condition that must be met for the update to occur.*/
The code can be used to overwrite the value of a column that exists in some of the tables. One needs to be careful if there are tables which have same names but different meaning! Review the tables and their values first!
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to
select records from a set of tables.
Output:
-- return all columns EXEC sp_MSForEachTable 'SELECT * FROM ?' -- return specific columns based on condition EXEC sp_MSForEachTable 'SELECT Column1, Column2 FROM ? WHERE YourCondition'
The second query will result in errors if the columns don't exist!
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to count
all the records from a table
Output:
-- retrieving the number of records for all tables EXEC sp_MSForEachTable 'SELECT ''?'' AS TableName, COUNT(*) AS RecordCount FROM ?'
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to
select all the records from tables containing the word invent.
Output:
DECLARE @sql NVARCHAR(MAX) SET @sql = '' EXEC sp_MSForEachTable @command1 = 'IF ''?'' LIKE ''%invent%'' BEGIN SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; '' END', @replacechar = '?', @command2 = 'EXEC sp_executesql @sql' PRINT @sql
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure when the
DataAreaId field exists in the table.
Output:
DECLARE @sql NVARCHAR(MAX) SET @sql = '' EXEC sp_MSForEachTable @command1 = 'IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''DataAreaId'') BEGIN SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; '' END', @replacechar = '?', @command2 = 'EXEC sp_executesql @sql' PRINT @sql
As can be seen, the query uses the metadata available to retrieve the tables in scope.
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to count
all the records for a subset of tables.
Output:
DECLARE @sql NVARCHAR(MAX) SET @sql = '' EXEC sp_MSForEachTable @command1 = 'IF ''?'' LIKE ''%subset%'' BEGIN SET @sql = @sql + ''SELECT ''''?'''' AS TableName, COUNT(*) AS RecordCount FROM '' + ''?'' + ''; '' END', @replacechar = '?', @command2 = 'EXEC sp_executesql @sql' PRINT @sql
Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to count all the records for a subset of tables based on a different column from each table.
Output:
DECLARE @sql NVARCHAR(MAX) SET @sql = '' EXEC sp_MSForEachTable @command1 = 'IF ''?'' LIKE ''%subset%'' BEGIN IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''Column1'') BEGIN SET @sql = @sql + ''SELECT ''''?'''' AS TableName, COUNT(Column1) AS RecordCount FROM '' + ''?'' + ''; '' END ELSE IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''Column2'') BEGIN SET @sql = @sql + ''SELECT ''''?'''' AS TableName, COUNT(Column2) AS RecordCount FROM '' + ''?'' + ''; '' END END', @replacechar = '?', @command2 = 'EXEC sp_executesql @sql' PRINT @sql
The output is not what I intended, though it looks interesting enough to give it a try...
Prompt:
Create a test table that contains a list of tables and corresponding fields from a table. There can be multiple columns available for each table. Write a query that uses the sp_MSForEachTable stored procedure to count all the distinct value for each combination of values available in the test table
Output:
-- Step 1: Create the Test Table -- Create the test table CREATE TABLE TestTable ( TableName NVARCHAR(128), ColumnName NVARCHAR(128) ); -- Insert sample data into the test table INSERT INTO TestTable (TableName, ColumnName) VALUES ('Table1', 'ColumnA'); INSERT INTO TestTable (TableName, ColumnName) VALUES ('Table1', 'ColumnB'); INSERT INTO TestTable (TableName, ColumnName) VALUES ('Table2', 'ColumnC'); INSERT INTO TestTable (TableName, ColumnName) VALUES ('Table2', 'ColumnD'); INSERT INTO TestTable (TableName, ColumnName) VALUES ('Table3', 'ColumnE'); --Step 2: Query to Count Distinct Values DECLARE @sql NVARCHAR(MAX) SET @sql = '' EXEC sp_MSForEachTable @command1 = 'DECLARE @table NVARCHAR(128), @column NVARCHAR(128) SELECT @table = TableName, @column = ColumnName FROM TestTable WHERE TableName = PARSENAME(''?'', 1) IF @column IS NOT NULL BEGIN SET @sql = @sql + ''SELECT ''''?'''' AS TableName, '''' + @column + '''' AS ColumnName, COUNT(DISTINCT '' + @column + '') AS DistinctCount FROM '' + ''?'' + ''; '' END', @replacechar = '?', @command2 = 'EXEC sp_executesql @sql' PRINT @sql
Use the following code to create a table for testing the above scripts. The scripts should be run in a small, ideally empty database in a non-productive environment!
Prompt:
Create a table that contains the DataAreaId and CompanyName and
populate it with 5 records.
Output:
-- Create the table CREATE TABLE Company ( DataAreaId INT, CompanyName NVARCHAR(100) ); -- Insert 5 records into the table INSERT INTO Company (DataAreaId, CompanyName) VALUES (1, 'Company A'); INSERT INTO Company (DataAreaId, CompanyName) VALUES (2, 'Company B'); INSERT INTO Company (DataAreaId, CompanyName) VALUES (3, 'Company C'); INSERT INTO Company (DataAreaId, CompanyName) VALUES (4, 'Company D'); INSERT INTO Company (DataAreaId, CompanyName) VALUES (5, 'Company E');
The queries should be run in SQL Server environment where the sp_MSForEachTable is available. In SQL databases the code will result in the following error:
"Msg 2812, Level 16, State 62, Line 1, Could not find stored procedure 'sp_MSForEachTable'."
Previous Post <<||>> Next Post
