Art of the DBA Rotating Header Image

sql scripts

Monday Scripts – Getting Partition Information

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

I’m going to follow up my post on partitioning with a query I wrote while implementing the partitions and cleaning up the empty ones. One of the issues I ran in to was clearly seeing each partition’s range and the number of rows within it. These two questions are answered in different places in the catalog views, sys.partition_range_values and sys.partitions. To build this out, I started with a nice little query from Derek Dieter, adding in a couple joins to get me to sys.partitions. Here’s the result!

select 
	t.name as TableName
	, ps.name as PartitionScheme
	, pf.name as PartitionFunction
	, p.partition_number
	, p.rows
	, case 
		when pf.boundary_value_on_right=1 then 'RIGHT' 
		else 'LEFT' 
	  end [range_type]
	, prv.value [boundary]
from sys.tables t
    join sys.indexes i on t.object_id = i.object_id
    join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
    join sys.partition_functions pf on ps.function_id = pf.function_id
    join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
    join sys.partition_range_values prv on pf.function_id = prv.function_id and p.partition_number = prv.boundary_id
where i.index_id < 2  --So we're only looking at a clustered index or heap, which the table is partitioned on
order by p.partition_number

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.

T-SQL Tuesday #17: Using APPLY

Apr 12th, 2011
by Mike Fal.
2 comments

I confess, I’m one of those guys who really didn’t know much about APPLY. Sure, I saw it in the occasional query I’d find on the ‘tubes, but I never really dug into what it was and why this piece of syntax was so important. Fortunately, this month’s T-SQL Tuesday has rolled around with an opportunity for me to do a little learning, so off to the Google-mobile!

Of course, you can easily find the MSDN article explaining apply, but there’s a much more detailed one I found on Simple Talk by Robert Sheldon. Conceptually, it’s pretty simple. APPLY, in both its CROSS APPLY and OUTER APPLY forms, allows you to call a table valued function for each row in your data set. In a lot of ways it’s like a join, but instead of two tables linked on a common field, you’re linking your query with a computed set of rows that you return with your function.

Now, I wish I could say I had some cool query to share, but tend to use APPLY in some simple ways to get query information. Here’s a query that makes use of the dynamic management functions