💎🤖SQL Reloaded: Copilot Stories (Part VII: Using the sp_MSForEachTable Undocumented Stored Procedures in SQL Server)

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