Showing posts with label T-SQL. Show all posts
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));
GO
                     
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
                      );                   
GO

INSERT INTO #myTable
EXEC ('sp_msforeachdb @command1="use [?]; dbcc showfilestats"');
GO
                     
INSERT INTO #mytable2
EXEC( 'dbcc sqlperf(logspace)');
GO

SELECT
((SUM(usedExtents)*64)/1024) AS DatabaseUsedSpaceinMB
,((SUM(totalExtents)*64)/1024) AS DatabaseSizeinMB
FROM #myTable;
GO

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;
 GO


SELECT
    *
FROM
    #myTable2
ORDER BY
    logsize DESC;
GO

DROP TABLE #myTable;

DROP TABLE #myTable2;
GO




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 [?]"');
GO

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

GO

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 + ']'
                       
                        insert into @myTable2(mySQLText)
                        values(@sqlcmd)
                       
                        set @sqlcmd='GO'
                       
                        insert into @myTable2(mySQLText)
                        values(@sqlcmd)
                       
                        set @sqlcmd ='IF  not EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+ ''''+ @Rolename+ ''''+ ' AND type = '+ ''''+ 'R' + ''''+ ')'
                       
                        insert into @myTable2(mySQLText)
                        values(@sqlcmd)
                       
                        set @sqlcmd='CREATE ROLE [' + @Rolename + '] AUTHORIZATION [dbo]'
                       
                        insert into @myTable2(mySQLText)
                        values(@sqlcmd)
                       
                        set @sqlcmd='GO'
                       
                        insert into @myTable2(mySQLText)
                        values(@sqlcmd)
                       
                  End
           
            set @sqlcmd = (select mySQLText from @myTable where myID=@x)
           
            insert into @myTable2(mySQLText)
            values(@sqlcmd)
           
            set @sqlcmd='GO'
           
            insert into @myTable2(mySQLText)
            values(@sqlcmd)
           
      End

--======================================================
--Query @myTable2 and get the fruits of your labor
--======================================================   
select mySQLText from @myTable2    

And here you go!

As I said in the comments above instead of using a second table variable you can just comment that out and where you insert values into table 1 you would just use a print command to get the script as something you can copy and paste

Thanks,

Brad