dcsimg

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

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers