SQL Server Database administrators often generate SQL Statements and execute the generated SQL statement in order to simplify certain tasks. It has always been a twin operation. This article illustrates how to use un-documented stored procedures to execute the generated SQL Statements directly.
Example 1
The following example generates a SQL statement to find the number of rows on each table.
Query
set quoted_identifier off
go
select ‘Select ”’+name+”’
as TableName,count(*)
as No_Of_Rows
from northwind.dbo.[‘+ name+’]’
from northwind.dbo.sysobjects
where type =’u’ order by name
go
Primary Result
Select ‘Categories’ as TableName,count
as No_Of_Rows
from northwind.dbo.[claire]
Select ‘CustomerCustomerDemo’
as TableName,count
as No_Of_Rows
from northwind.dbo.
[CustomerDemographics]
Select ‘Customers’ as TableName,count
as No_Of_Rows
from northwind.dbo.[dept]
Select ’emp’ as TableName,count
as No_Of_Rows
from northwind.dbo.[emp2]
Select ‘Employees’ as TableName,count
as No_Of_Rows
from northwind.dbo.[EmployeeTerritories]
Select ‘myfile’ as TableName,count
as No_Of_Rows
from northwind.dbo.[mytable]
Select ‘mytabled’ as TableName,count
as No_Of_Rows
from northwind.dbo.[Order Details]
Select ‘Orders’ as TableName,count
as No_Of_Rows
from northwind.dbo.[Products]
Select ‘Region’ as TableName,count
as No_Of_Rows
from northwind.dbo.[Shippers]
Select ‘Suppliers’ as TableName,count
as No_Of_Rows
from northwind.dbo.[Territories]
Select ‘x’ as TableName,count
Usually Database administrators generate the above statement and execute the generated statement.
Secondary result (partial)
TableName No_Of_Rows
———- ———–
Categories 8
TableName No_Of_Rows
——— ———–
claire 7
TableName No_Of_Rows
——————– ———–
CustomerCustomerDemo 0
Instead, this can be simplified and consolidated using the un-documented extended procedure xp_execresultset in order to produce the secondary result directly.
Query
set quoted_identifier off
go
xp_execresultset “select ‘Select ”’+name+”’
as TableName,count(*)
as No_Of_Rows from northwind.dbo.[‘+ name+’]’
from northwind.dbo.sysobjects
where type =’u’ order by name”,”Northwind”
go
Secondary result (partial)
TableName No_Of_Rows
———- ———–
Categories 8
TableName No_Of_Rows
——— ———–
claire 7
TableName No_Of_Rows
——————– ———–
CustomerCustomerDemo 0
Example 2
The following example generates a script that adds a particular user to every database and adds that user to two different roles, “db_datareader” and “db_denydatawriter.”
set quoted_identifier off
go
select ‘use [‘+name +’]’+ char(13)+’Go’+char(13)+’sp_adduser
”xx” ‘+char(13)+’Go’+char(13) + ‘sp_addrolemember ”db_datareader”,
”xx”’ +char(13)+’Go’ +char(13) + ‘ sp_addrolemember ”db_denydatawriter”,
”xx”’ +char(13)+’Go’+char(13) from master..sysdatabases
go
Primary result
use [master]
Go
sp_adduser ‘xx’
Go
sp_addrolemember ‘db_datareader’,’xx’
Go
sp_addrolemember ‘db_denydatawriter’,’xx’
Go
use [tempdb]
Go
sp_adduser ‘xx’
Go
sp_addrolemember ‘db_datareader’,’xx’
Go
sp_addrolemember ‘db_denydatawriter’,’xx’
Go
use [model]
Go sp_adduser ‘xx’
Go
sp_addrolemember ‘db_datareader’,’xx’
Go
The above generated statement can be executed separately in order to add the user to db_datareader and db_denydatawriter role in every database.
Secondary result
Granted database access to ‘xx’.
‘xx’ added to role ‘db_datareader’.
‘xx’ added to role ‘db_denydatareader’.
Granted database access to ‘xx’.
‘xx’ added to role ‘db_datareader’.
‘xx’ added to role ‘db_denydatareader’.
This can be simplified and consolidated by using the un-documented extended procedure sp_msforeachdb in order to produce the secondary result directly.
set quoted_identifier off
go
sp_msforeachdb “begin use [?] end
begin exec sp_adduser ‘xx’ end
begin exec sp_addrolemember ‘db_datareader’,’xx’ end
begin exec sp_addrolemember ‘db_denydatareader’,’xx’ end ”
go
Result
Granted database access to ‘xx’.
‘xx’ added to role ‘db_datareader’.
‘xx’ added to role ‘db_denydatareader’.
Granted database access to ‘xx’.
‘xx’ added to role ‘db_datareader’.
‘xx’ added to role ‘db_denydatareader’.
Granted database access to ‘xx’.
‘xx’ added to role ‘db_datareader’.
‘xx’ added to role ‘db_denydatareader’.
Granted database access to ‘xx’.
‘xx’ added to role ‘db_datareader’.
‘xx’ added to role ‘db_denydatareader’.
Example 3
The following query generates a sql statement that gathers filestatus information on every database.
use master
go
create table ##x
(fileid int,
filegroup int,
totalextents bigint,
usedextents bigint,
name varchar(2000),
FileName varchar(2000))
go
set quoted_identifier off
go
select ‘begin use [‘+name+’] end
begin insert ##x exec (“dbcc showfilestats”) end ‘
from sysdatabases
go
Primary result
begin use [master] end begin insert
begin use [tempdb] end begin insert
begin use [model] end begin insert
begin use [msdb] end begin insert
begin use [pubs] end begin insert
begin use [Northwind] end begin insert
begin use [DB1] end begin insert
begin use [DB2] end begin insert
begin use [distribution] end begin insert
begin use [Workstation_Reboot] end begin insert
begin use [testlava] end begin insert
begin use [commissioner] end begin insert
begin use [WeeklyReboot] end begin insert
begin use [websearch] end begin insert
begin use [testdep] end begin insert
begin use [ServerCPUUsage] end begin insert
begin use [disasterrecovery] end begin insert
begin use [SecurityLog] end begin insert
begin use [Bank2] end begin insert
Secondary result (partial)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now the global temporary table ##X will hold the result of filestat of all the databases.
Select * from ##x
1 |
1 |
271 |
253 |
master |
D:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf |
1 |
1 |
576 |
14 |
tempdev |
D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf |
1 |
1 |
400 |
10 |
modeldev |
D:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf |
1 |
1 |
316 |
281 |
MSDBData |
D:\Program Files\Microsoft SQL Server\MSSQL\data\msdbdata.mdf |
1 |
1 |
160 |
26 |
pubs |
D:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf |
1 |
1 |
71 |
49 |
Northwind |
D:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.mdf |
1 |
1 |
47 |
14 |
DB1 |
D:\Program Files\Microsoft SQL Server\MSSQL\data\DB1.mdf |
1 |
1 |
14 |
14 |
DB2 |
D:\Program Files\Microsoft SQL Server\MSSQL\data\DB2.mdf |
1 |
1 |
49 |
46 |
distmodel |
D:\Program Files\Microsoft SQL Server\MSSQL\Data\distribution.MDF |
1 |
1 |
11 |
11 |
Workstation_Reboot |
D:\Program Files\Microsoft SQL Server\MSSQL\data\Workstation_Reboot.mdf |
1 |
1 |
10 |
10 |
testlava |
D:\Program Files\Microsoft SQL Server\MSSQL\data\testlava.mdf |
1 |
1 |
11 |
11 |
commissioner |
D:\Program Files\Microsoft SQL Server\MSSQL\data\commissioner.mdf |
1 |
1 |
11 |
11 |
WeeklyReboot |
D:\Program Files\Microsoft SQL Server\MSSQL\data\WeeklyReboot.mdf |
1 |
1 |
10 |
10 |
websearch |
D:\Program Files\Microsoft SQL Server\MSSQL\data\websearch.mdf |
1 |
1 |
11 |
11 |
testdep |
D:\Program Files\Microsoft SQL Server\MSSQL\data\testdep.mdf |
1 |
1 |
18 |
17 |
ServerCPUUsage |
D:\Program Files\Microsoft SQL Server\MSSQL\data\ServerCPUUsage.mdf |
1 |
1 |
22 |
21 |
DisasterRecovery_Data |
c:\DisasterRecovery_data.ldf |
1 |
1 |
400 |
13 |
SecurityLog |
D:\Program Files\Microsoft SQL Server\MSSQL\data\SecurityLog.mdf |
1 |
1 |
400 |
11 |
Bank2 |
D:\Program Files\Microsoft SQL Server\MSSQL\data\Bank2.mdf |
This can be simplified and consolidated by using the un-documented extended procedure sp_msforeachdb in order to produce the secondary result directly.
use master
go
create table ##x
(fileid int,
filegroup int,
totalextents bigint,
usedextents bigint,
name varchar(2000),
FileName varchar(2000))
go
set quoted_identifier off
go
sp_msforeachdb “begin use [?] end begin insert ##x exec (‘dbcc showfilestats’) end ”
go
select * from ##x
Conclusion
This article has demonstrated how to use un-documented stored procedures to execute a result set.
» See All Articles by Columnist MAK
