Executing the result set

August 16, 2005

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.[Categories]
Select 'claire' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[claire]
Select 'CustomerCustomerDemo' 
 as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.
   [CustomerCustomerDemo]
Select 'CustomerDemographics' 
 as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.
   [CustomerDemographics]
Select 'Customers' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[Customers]
Select 'dept' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[dept]
Select 'emp' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[emp]
Select 'emp2' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[emp2]
Select 'Employees' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[Employees]
Select 'EmployeeTerritories' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[EmployeeTerritories]
Select 'myfile' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[myfile]
Select 'mytable' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[mytable]
Select 'mytabled' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[mytabled]
Select 'Order Details' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[Order Details]
Select 'Orders' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[Orders]
Select 'Products' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[Products]
Select 'Region' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[Region]
Select 'Shippers' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[Shippers]
Select 'Suppliers' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[Suppliers]
Select 'Territories' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[Territories]
Select 'x' as TableName,count(*) 
 as No_Of_Rows 
 from northwind.dbo.[x]

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 ##x exec ("dbcc showfilestats") end 
begin use [tempdb] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [model] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [msdb] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [pubs] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [Northwind] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [DB1] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [DB2] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [distribution] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [Workstation_Reboot] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [testlava] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [commissioner] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [WeeklyReboot] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [websearch] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [testdep] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [ServerCPUUsage] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [disasterrecovery] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [SecurityLog] end begin insert ##x exec ("dbcc showfilestats") end 
begin use [Bank2] end begin insert ##x exec ("dbcc showfilestats") end

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

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