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

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