SQLBalls: T-SQL

Showing posts with label T-SQL. Show all posts

Monday, March 7, 2011

T-SQL Tuesday 16: Aggregation

So it’s that time again, T-SQL Tuesday. This week our event is Sponsored by Jes Borland (Blog|Twitter). So Jes what’s the subject?

“I want to hear how you solved business problems with aggregate functions. I want to see your cool T-SQL tricks. How are aggregates used in SSRS, SSAS, or SSIS? What have you learned about aggregate functions?”

So Dear Reader, Aggregation it is!

SOME THINGS JUST GO TOGETHER

We’ve all probably done our fair share of fancy aggregates, I have to say that most of the code I’ve used aggregates for is for business functionality and sitting with clients that I’ve worked for in the past. I think we all have a nightmare report, script, or task that we accomplished using aggregates and while those things make for interesting stories, they aren’t that useful. I’d like to give you something that you can use, (if you don’t already have a script to do what I’ve done here).

Sometimes the most useful things are simple, like PB & J (simple and delicious!). Here are a couple of queries that I use that have the SUM aggregate function in them. Simple but effective.

The first is a quick script that extracts the information from DBCC SHOWFILESTATS and uses the "undocumented" sp_msforeachdb in order to gather total space used by all datafiles on an instance, total space reserved for all data files on an instance, then a breakdown by datafile per database, lastly we take the info from DBCC SQLPERF(LOGSPACE)and sort by the log file size.

The second script is of the same concept. You can use the built-in reports “Disk Usage by Tables” to get the same info, but sometimes it’s just nice to build a script to work for you. This script uses sp_spaceused and the "undocumented" sp_msforeachtable in order to build a list of tables by row number and the Reserved size for Data. (I use the Second script on my Compression demos to help you find an appropriate table (size wise there are multiple steps J) while examining compression). Then I do a SUM function on each of the fields to show an aggregate of your database by user table.

SCRIPT1 – Get Database Size Stats

/*============================================================

--@SQLBalls: SQLBalls@gmail.com

--http://www.SQLBalls.com

--T-SQL TUESDAY 16 Aggregation

--This Sample Code is provided for the purpose of illustration only

--and -is not intended to be used in a production environment.

--THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS"

--WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING

--BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR

--FITNESS FOR A PARTICULAR PURPOSE.

==============================================================*/

IF exists(SELECT * FROM tempdb.sys.tables WHERE name like '#myTable%' and name not like'#myTable2%')

BEGIN

DROP TABLE #myTable;

END

CREATE TABLE #myTable (

[myID] [int] IDENTITY(1,1) NOT NULL,

fileid varchar(4),

fileGroup varchar(4),

TotalExtents int,

usedExtents int,

DBName sysname,

filename1 varchar(8000));

IF exists(SELECT * FROM tempdb.sys.tables WHERE name like '#myTable2%')

BEGIN

DROP TABLE #myTable2;

END

CREATE TABLE #myTable2(

dbName sysname,

logSize float,

spaceUsed float,

dbStatus int

);

INSERT INTO #myTable

EXEC ('sp_msforeachdb @command1="use [?]; dbcc showfilestats"');

INSERT INTO #mytable2

EXEC( 'dbcc sqlperf(logspace)');

SELECT

((SUM(usedExtents)*64)/1024) AS DatabaseUsedSpaceinMB

,((SUM(totalExtents)*64)/1024) AS DatabaseSizeinMB

FROM #myTable;

SELECT

DBName

,usedExtents

,TotalExtents

,((usedExtents*64)/1024) AS DatabaseUsedSpaceinMB

,((totalExtents*64)/1024) AS DatabaseSizeinMB

,(((totalExtents*64)/1024)-((usedExtents*64)/1024)) as DataFileFreeSpace

FROM

#mytable

ORDER BY

((totalExtents*64)/1024) DESC;

SELECT

FROM

#myTable2

ORDER BY

logsize DESC;

DROP TABLE #myTable;

DROP TABLE #myTable2;

SCRIPT2-Get Table Size Stats

/*============================================================

--@SQLBalls: SQLBalls@gmail.com

--http://www.SQLBalls.com

--T-SQL TUESDAY 16 Aggregation

--This Sample Code is provided for the purpose of illustration only

--and -is not intended to be used in a production environment.

--THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS"

--WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING

--BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR

--FITNESS FOR A PARTICULAR PURPOSE.

==============================================================*/

IF EXISTS(SELECT * FROM TEMPDB.sys.tables WHERE name like '#myTable%')

BEGIN

DROP TABLE #myTable

END

CREATE TABLE #myTable(

[Name] sysname,

[Rows] int,

[Reserved] varchar(500),

[Data] varchar(500),

[Index_Size] varchar(500),

[Unused] varchar(500)

);

INSERT INTO #myTable

EXEC ('sp_msforeachtable @command1="sp_spaceused [?]"');

SELECT * FROM #myTable

ORDER BY

[rows] DESC,

LEFT([reserved],(LEN([reserved])-3)) DESC;

SELECT

SUM(CAST(LEFT([reserved],(LEN([reserved])-3)) as int)) as [Total_Reserved(KB)]

,SUM(CAST(LEFT([data],(LEN([data])-3)) as int)) as [Total_Data(KB)]

,SUM(CAST(LEFT([Index_Size],(LEN([Index_Size])-3)) as int)) as [Total_IndexData(KB)]

,SUM(CAST(LEFT([Unused],(LEN([Unused])-3)) as int)) as [Total_UnusedSpace(KB)]

FROM #myTable

DROP TABLE #myTable;

Happy T-SQL Tuesday I hope you enjoy the scripts!

Thanks,

Brad

Tuesday, November 9, 2010

Dynamic Permissions

I needed to script out a database user role and all of the permissions that this role has with it for a server migration. We are moving the databases from on server to a new location. You could try the Generate script tool, but while that would give me the script to re-create the role it did not give me all of the rights that role currently has.

I opened up the advanced option and still not luck.

So I did a quick Google search on how to script out roles and found an old post by SQL MVP Russell Fields where he was answering a question on the SQL Security Forum, http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/threads.

Russell’s Response, http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/26ffcac4-8d4c-4107-b355-80e63344fb8e listed how to use the DMO’s sys.database_permissions and sys.database_principals in order to query a database to find out the roles that where listed for the database, as well as the permissions each of those roles granted.

So my first step is most of the way done. I now know how to get the info, now I want to dynamically generate a script that will re-create this role for me, and allow me to use this on multiple databases.

One thing I will need to do is add the schema to the return results, as we will want to make sure our object mappings are correct. So I’ll add sysobjects and the schema_name() function to this query to pull that back. My first results look like this.

select

u.name,

p.permission_name,

p.class_desc,

schema_name(o.uid),

object_name(p.major_id) ObjectName,

state_desc

from

sys.database_permissions p

join sys.database_principals u

on p.grantee_principal_id = u.principal_id

join sysobjects o

on p.major_id=o.id

where

u.name='DatabaseRoleName'

order by

ObjectName, name, p.permission_name

You will want to fill in the Database role you are looking for in the Where clause, or remove the Where to get a full listing of all the permissions for all of the roles in the database you are querying.

So now we’ve got everything we want, now I need to string it all together into the syntax I would like.

select

u.name,

p.permission_name,

p.class_desc,

schema_name(o.uid),

object_name(p.major_id) ObjectName,

state_desc,

(state_desc + ' ' + p.permission_name + ' ON ' + '['+schema_name(o.uid)+'].['+object_name(p.major_id)+'] TO ' + u.name) As PermissionText

from

sys.database_permissions p

join sys.database_principals u

on p.grantee_principal_id = u.principal_id

join sysobjects o

on p.major_id=o.id

where

u.name='DatabaseRoleName'

order by

ObjectName, name, p.permission_name

This gives me exactly what I want syntax wise, but now I need to insert “GO” in between each row, and I would like to make sure and specify the database that I’m working in, so I have a full script generator.

So now I’ll flush out the script a little bit, I need to do some row by row alterations, and I’m not looking at a large set of data, so I’ll use a table variable to hold all the syntax that I’m going to generate.

--======================================================

--Declare our variables

--======================================================

declare @dbname varchar(500), @i int, @x int, @sqlcmd varchar(max), @RoleName nvarchar(500)

--======================================================

--Get the Current DB Name

--======================================================

set @dbname = (select DB_NAME())

set @Rolename = N'DatabaseRoleName'

--======================================================

--Declare the 2 Table Variables I'll be using

--======================================================

declare @myTable as Table(

[myID] [int] IDENTITY(1,1) NOT NULL,

mySQLText varchar(max)

)

declare @myTable2 as Table(

[myID] [int] IDENTITY(1,1) NOT NULL,

mySQLText varchar(max)

)

--======================================================

--Populate @myTable

--======================================================

insert into @myTable

select

(state_desc + ' ' + p.permission_name + ' ON ' + '['+schema_name(o.uid)+'].['+object_name(p.major_id)+'] TO ' + u.name) As PermissionText

from

sys.database_permissions p

join sys.database_principals u

on p.grantee_principal_id = u.principal_id

join sysobjects o

on p.major_id=o.id

where

u.name=@Rolename

--======================================================

--Set the @i count = to the count from @mytable

--Set @x = 0

--======================================================

set @i = (select count(*) from @myTable)

set @x=0

--======================================================

--Begin the loop to create our dynamic statements

--you can either use table 2 or you could print the values

--to have a text you could just copy and paste

--this will work either way

--======================================================

while @x < @i

Begin

set @x= @x +1

if (@x=1)

Begin

set @sqlcmd= 'Use [' + @dbname + ']'