Executing the result set

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









































































































































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

Latest Articles