Art of the DBA Rotating Header Image

dynamic SQL

Monday Scripts – xp_logininfo

Apr 18th, 2011
by Mike Fal.
4 comments

How many times have you wished you could use SQL Server to query Active Directory and see who is in that group? This week’s script will allow you to do that, so if you have AD groups set up for logging into your server you can see exactly which logins have access.

xp_logininfo is an extended stored procedure that does just that. By passing a couple parameters, you can easily query any AD group that is a server principal. Wrap it up in a dynamic SQL loop, and you can quickly and easily audit your server’s security.

declare @winlogins table
(acct_name sysname,
acct_type varchar(10),
act_priv varchar(10),
login_name sysname,
perm_path sysname)

declare @group sysname

declare recscan cursor for
select name from sys.server_principals
where type = 'G' and name not like 'NT%'

open recscan
fetch next from recscan into @group

while @@FETCH_STATUS = 0
begin
insert into @winlogins
exec xp_logininfo @group,'members'
fetch next from recscan into @group
end
close recscan
deallocate recscan

select
r.name,
u.name,
u.type_desc,
wl.login_name,
wl.acct_type
from (select * from sys.server_principals where type = 'R') r
join sys.server_role_members rm on (r.principal_id = rm.role_principal_id)
join (select * from sys.server_principals where type != 'R') u on rm.member_principal_id = u.principal_id
left join @winlogins wl on u.name = wl.perm_path
order by login_name,r.principal_id,u.type_desc,u.name

Looking at the script, I first do a looped scan through sys.server_principals for all Windows groups (type ‘G’) and collect their AD group info. I store that in a table variable, then join it back to a select from server_principals again for a full listing of all my server permissions. Bam! Instant security report.

Monday Scripts – Free Space by Drive

Apr 11th, 2011
by Mike Fal.
No comments yet

Here’s a quick script that will allow you to gather up your free space by drive on your server. You can use this to evaluate your space usage and address immediate concerns. It’s a straightforward query that uses dynamic SQL to query the files in each database’s sys.database_files view and the SpaceUsed property collected by the FILEPROPERTY function.

declare @return table
(drive_letter varchar(100),
 allocated_space decimal(15,2),
 used_space decimal(15,2),
 free_space decimal(15,2))

declare recscan cursor for
select name from sys.databases
where state = 0

declare @db varchar(100)
declare @sql varchar(max)

open recscan
fetch next from recscan into @db

while @@FETCH_STATUS = 0
begin
	set @sql = 	'use ['+@db+'] '+
				'SELECT	UPPER(SUBSTRING(filename,1,2)) ,'+
				'sum(a.Size)/128.0,'+
				'sum(FILEPROPERTY(a.Name,''SpaceUsed''))/128.0,'+
				'sum(a.Size-FILEPROPERTY(a.Name,''SpaceUsed''))/128.0'+
				'FROM ['+@db+'].dbo.sysfiles a ' +
				'GROUP BY SUBSTRING(filename,1,2)'
	insert @return
	exec(@sql)

	fetch next from recscan into @db
end

close recscan
deallocate recscan

select
	drive_letter,
	sum(allocated_space) total_alocated,
	sum(used_space) total_used,
	sum(free_space) total_free,
	sum(free_space)/sum(allocated_space) perc_free
from @return
group by drive_letter
order by drive_letter

The main trick of this and the reason why we can’t use sys.master_files is because FILEPROPERTY only provides data for the current database context. If you try to call FILEPROPERTY for a file not in your current database, it will return a NULL.  As a result, we need to dynamically switch database contexts and gather the data for all of our databases.

Getting Started with Dynamic SQL

Mar 30th, 2011
by Mike Fal.
No comments yet

Since two of the scripts I’ve posted so far on Mondays involve the use of dynamic SQL code, I figured it would be a worthwhile to post on using dynamic SQL in SQL Server. After all, understanding and using dynamic SQL is one of the fundamental tools for a DBA to make management tasks automated, helping you save time and eliminate “fat fingers”.

What is dynamic SQL?

Before we get started, let’s define what we’re talking about. A quick Googling gives us a bunch of links that all describe the same process: “Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed”. What this means to us is now we can take SQL code that we want to run repeatedly with some changing parts, and write it in a way where those changes can be handled by other logic and keep the rest of the query unchanged, saving us a whole lot of time and effort.

Parametrization and code reuse

We’ll use my sp_spaceused script to show dynamic SQL in action. First off, we should review what the objective of that script is: running sp_spaceused to give us information about the size of the tables in our database in context with the other tables. Since the stored procedure only gives us information one table at a time, we need a way to gather all that information without having to run each query separately. We know that because the stored procedure accepts the table name as a parameter, the core code will remain the same no matter what we pass to it, so we only need to handle the changing table name.

Let’s look at the code:

declare recscan cursor for
select name from sysobjects where type = 'U'

open recscan
fetch next from recscan into @curr
while @@fetch_status = 0

begin
insert into @return
exec sp_spaceused @curr

fetch next from recscan into @curr
end

In the first two lines, we have a SQL cursor. If you need more information on cursors and how they work, hit up MSDN, but the short version is they give us a way to create a data set that we can use programmatically for processing. In this specific case, I’m building a data set of all the user tables in my database.

The next part is to open our list of table names and grab the first item in it. I’ll take that table name (the next available value in my cursor) and stuff it into a variable. Now I can use the stored procedure, with my variable as a parameter, to execute the code and get my return set. The rest of a the code is a simple loop that will continue until the SQL engine can no longer fetch a value from the cursor.

At the end of the day, we have two pieces that make this work:

  • A static set of code that performs the same task, but with a parametrized value that can alter the target of that task.
  • A programatically changing data set that we can use to provide the parameter for our code statement, so we can apply the same work to different targets.

But is this really dynamic SQL, since all we’re doing is using a stored procedure driven by a changing parameter? Sure thing, because while the stored procedure code remains the same, we are changing the parameter programmatically. We aren’t limited to just using stored procedures, though. While there are a lot of advantages to using stored procedures, such as pre-compiled SQL statements and making use of the procedure cache, sometimes you need something more fine grained. Fortunately, SQL server provides us with a couple other methods to execute dynamic SQL.

EXECution time

We can also use the EXECUTE function for more than just calling a stored procedure. It will also accept a SQL statement passed to it as a string. Let’s see how this works by modifying our script to get the number of tables in each database instead of the space used:

declare @sql varchar(max)
declare @db varchar(100)
declare @return table
(db_name varchar(100),
table_count int)

declare recscan cursor for
select name from sysdatabases where dbid >4

open recscan
fetch next from recscan into @db
while @@fetch_status = 0
begin
set @sql = 'select '''+@db+''',count(*) from '+@db+'.sys.objects where type = ''U'''

insert into @return
exec(@sql)
end

close recscan
deallocate recscan

select * from @return

Now we can take our SQL statement and change the database context programmatically, allowing us to reuse the same query over and over again.

SQL Server also provides the sp_executesql stored procedure as an alternative to using EXECUTE with two major differences. The first is that sp_executesql allows you to use parameters within your dynamic statement, so it allows you a cleaner option for parametrization. The second is you can use it to capture an output parameter, giving you some other options for using the data, though you are limited in using the statement output within an insert.

Finally, we can also make use of two undocumented stored procedures in SQL server, sp_MSforeachtable and sp_MSforeachdb. Just like they’re names imply, you can use these stored procedures to execute a SQL statement against every table in a database or every database on a server. While they don’t offer you as much control over your execution, they are pretty handy.

I’ve found dynamic SQL to be a huge time saver, since it is so flexible and powerful. With it, I can build out scripts to perform any number of tasks that remain the same, giving me the security in knowing that the task will run the same way, no matter how my parameters change.  Keep in mind that, while we only used SELECTs for the examples, you can use any SQL statement you normally would write ad hoc. For additional information and examples, I highly recommend Introduction to Dynamic SQL on SQLTeam.com, which will provide you more detailed information on the use of the different methods, as well as permissions and scope.