Wednesday, January 26, 2011

Single-line Rowcounts

This is just another clever way to use the COUNT function, returning two distinct counts in the same single line result-set.  This example is a call center, I just want to get the number of successful calls, and disconnected calls, by day (call date)... in one query, not two:

  --working table variable to store your results
  DECLARE @counts TABLE (
  CallDate CHAR(10),SuccesfulCalls INT,DisconnectedCalls INT
  )
  INSERT @counts (
  CallDate,SuccesfulCalls,DisconnectedCalls
  )
  --collect both counts joined by calldate
  SELECT 
   a.[Call Date],a.[Successful Calls],b.[Disconnected Calls]
FROM
(
 SELECT 
CONVERT(VARCHAR(12),c.CallDate,101) [Call Date],
COUNT(1) [Successful Calls]
 FROM
dbo.Call_Table c
 WHERE
c.callstatus <> 'Disconnected'
 GROUP BY
CONVERT(VARCHAR(12),c.CallDate,101)
) a
INNER JOIN
(
 SELECT 
CONVERT(VARCHAR(12),c.CallDate,101) [Call Date],
COUNT(1) [Disconnected Calls]
 FROM
dbo.Call_Table c
 WHERE
c.callstatus = 'Disconnected'
 GROUP BY
CONVERT(VARCHAR(12),c.CallDate,101)
) b
ON a.[Call Date] = b.[Call Date]
ORDER BY a.[Call Date]


--select back out from your working table variable
SELECT 
CONVERT(CHAR(12),[CallDate],120) [Call Date],
SuccesfulCalls [Successful Calls],
DisconnectedCalls [Disconnected Calls]
FROM
@counts
ORDER BY 
CONVERT(DATETIME,[CallDate],101)


Example Results:
CallDate     SuccessfulCalls DisconnectedCalls 
 10/28/2010        125             2 
 11/4/2010         117             1 
 11/19/2010        181             1 
 11/26/2010        148             1 
 12/3/2010         105             2 
 12/8/2010         107             1 
 1/7/2011          194             1 
 1/17/2011         343             6 
 1/18/2011         219            31 
 1/19/2011         190            68 
 1/20/2011         183            83 
 1/21/2011         199           125 
 1/24/2011         230           155 
 1/25/2011         173           184 
 1/26/2011         104           203  


TIP:
You can't order by a date converted to string, if it is not in YYYYMMDD format.  That's why we have to do our ORDER BY like this:    CONVERT(DATETIME,[CallDate],101)

Monday, January 24, 2011

Are DBCC's being done?

DBCC CHECKDB (Transact -SQL)       per Books Online
Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:
  • Runs DBCC CHECKALLOC on the database.
  • Runs DBCC CHECKTABLE on every table and view in the database.
  • Runs DBCC CHECKCATALOG on the database.
  • Validates the contents of every indexed view in the database.
  • Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
  • Validates the Service Broker data in the database.


aka, Just Do It.

Ok, that's not fair.  You'd need to be in the SQL seat for a good amount of time before you understand the woes of not doing it.  (if you're lucky)  I strongly advocate using CHECKDB hand in hand with a strong backup strategy, to protect the integrity and stability of your database(s).  It can alert you of problems you weren't even aware of, and it can repair problems, if needed.  And please remember, disk failures CAN impact the integrity and availability of your data.  It can even corrupt your data.  CHECKDB will bring this to your attention.

Anyway, if you inherit some servers, you may want to see if DBCC's are being run:
  CREATE TABLE #working ([Parent] VARCHAR(255),[Object]    
  VARCHAR(255),[Field] VARCHAR(255),[Value] VARCHAR(255)   )
  CREATE TABLE #DBCCdetails (ServerName VARCHAR(255),DatabaseName    
  VARCHAR(255),LastRan DATETIME   )   
    
  EXEC master.dbo.sp_MSforeachdb       
     @command1 = 'USE [?] INSERT INTO #working EXECUTE (''DBCC  
  DBINFO WITH TABLERESULTS'')',
     @command2 = 'INSERT INTO #DBCCdetails SELECT @@SERVERNAME, 
  ''?'', Value FROM #working WHERE Field = 
  ''dbi_dbccLastKnownGood''',
     @command3 = 'TRUNCATE TABLE #working'
   
  ;WITH DBCC_CTE AS
  (
    SELECT ROW_NUMBER() OVER (PARTITION BY ServerName,  
    DatabaseName,LastRan ORDER BY LastRan) RowID
    FROM #DBCCdetails
  )
    DELETE FROM DBCC_CTE WHERE RowID > 1;
   
    SELECT        
       ServerName,
       DatabaseName,       
       CASE LastRan WHEN '1900-01-01 00:00:00.000' 
         THEN 'DBCC CHECKDB has NEVER been run.' 
       ELSE CAST(LastRan AS VARCHAR) END AS LastRan
    FROM #DBCCdetails
    ORDER BY 3;
   
    DROP TABLE #working;
    DROP TABLE #DBCCdetails;

I would do it, anyway.  In fact, I do.  I use this, or a version of it, every time I inherit new databases.


NOTES:

  • The TRUNCATE in @command3 is necessary due to a bug in v2008 that produces duplicates with the DBINFO insert.  
  • DBCC CHECKDB is much heavier in v2008 than it was in previous engines, due to the more comprehensive logical checks, and more complex underlying structures.  MSFT recommends using PHYSICAL_ONLY on larger databases, for a shorter runtime.  The difference in overhead is very notable!  I commonly use PHYSICAL_ONLY for the intra-week maintenance, and do a full one on Saturdays.  

RECOVERY MODEL, Recovery ... Recoverable?

On more than one occasion, I have inherited a bunch of pre-existing databases, and had to spend endless hours cleaning up the mess.  As soon as the servers become my responsibility, I start at the baseline -- standard maintenance and administration, and recovery requirements. 

What is the RECOVERY MODEL for each database?
  SELECT name [Database Name],
  recovery_model_desc [Recovery Model]
  FROM sys.databases
  WHERE database_id > 4
  GO


Backups... are they even being done?  (oooh, I could tell you some stories...)  This is a quick peek into msdb.dbo.backupsets, to return whatever you've got (FULL, DIFF, LOG), ordered by the backup date DESC.
  SELECT sys.name [Database],bkup.backup_finish_date   
  [BackupDate], 
  CASE WHEN TYPE ='D' THEN 'FULL' 
      WHEN TYPE ='I' THEN 'DIFFERENTIAL'
      WHEN TYPE ='L' THEN 'LOG' END [Type], 
  CEILING(bkup.backup_size/1048576) [Size (MB)],
  SERVER_NAME,
  USER_NAME [ByWhom]
  FROM master.dbo.sysdatabases sys 
  LEFT OUTER JOIN msdb.dbo.backupset bkup 
    ON bkup.database_name = sys.name  
  ORDER BY backup_finish_date DESC;


There are many, many more, but that is typically where I start.  Here are a couple more good ones.  Please check them out and let me know if you have any questions. 


Database File Size (MB)

How many times have you run EXEC sp_helpfile, and then had to paste the size returned into some online converter, in order to get your file size back in MB?  Or even GB?  No matter how long you've been doing this... it happens.  Here's a very quick method for you to query sys.master_files, computing the KB size into MB, for your data and log files:

SELECT 
DB_NAME(database_id) [DatabaseName],
[name] AS [LogicalName],
(size*8)/1024 [MB]
FROM 
sys.master_files

Results:


          DatabaseName          LogicalName                        MB 
          AdventureWorks         AdventureWorks_Data            186
          AdventureWorks          AdventureWorks_Log          25
              master                        master                            50
              master                       mastlog                            10
              tempdb                    tempdev                         1024
              tempdb                     templog                           256
                model                   modeldev                             10
                model                    modellog                               2
                 msdb                    MSDBData                          150
                 msdb                     MSDBLog                            25

Throw this WHERE clause in there, if you'd like to exclude the system databases from your result-set:

WHERE
database_id > 4


This statement just provides a little more detail for the database you are connected to:


    -- Individual File Size query
    SELECT 
       name AS [FileName], 
       file_id [FileID],
       physical_name AS [PhysName], 
       size/128 AS [TotalMB],
       size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 [AvailMB]
    FROM 
       sys.database_files;


    FileName          FileID    PhysName                                       TotalMB   AvailMB 
    MDW_primary 1         C:\MSSQL\Data\MDW_primary.mdf      10          8.750000
    MDW_log             2         C:\MSSQL\Log\MDW_log.ldf                10          9.656250
    MDW_data 3         C:\MSSQL\Data\MDW_data.ndf          200       198.562500


Sunday, January 9, 2011

DATEPART returns three character month names, date and year.

Just a quick way to CASE the output of the current date, to include the Month Name, as opposed to the numeric digit.  Today is 1/9/2011, this is my result:

    Date
    Jan 9, 2011

SELECT 'Date' = 
CASE
WHEN (DATEPART(MONTH,GETDATE()) = 1 ) THEN 'Jan'
WHEN (DATEPART(MONTH,GETDATE()) = 2 ) THEN 'Feb'
WHEN (DATEPART(MONTH,GETDATE()) = 3 ) THEN 'Mar'
WHEN (DATEPART(MONTH,GETDATE()) = 4 ) THEN 'Apr'
WHEN (DATEPART(MONTH,GETDATE()) = 5 ) THEN 'May'
WHEN (DATEPART(MONTH,GETDATE()) = 6 ) THEN 'Jun'
WHEN (DATEPART(MONTH,GETDATE()) = 7 ) THEN 'Jul'
WHEN (DATEPART(MONTH,GETDATE()) = 8 ) THEN 'Aug'
WHEN (DATEPART(MONTH,GETDATE()) = 9 ) THEN 'Sep'
WHEN (DATEPART(MONTH,GETDATE()) = 10 ) THEN 'Oct'
WHEN (DATEPART(MONTH,GETDATE()) = 11 ) THEN 'Nov'
WHEN (DATEPART(MONTH,GETDATE()) = 12 ) THEN 'Dec'
END + ' ' + DATENAME(dd,GETDATE()) + ', '+ DATENAME(yyyy,GETDATE())

Different DATETIME formats.

DBAs are always looking for different ways to return the date.  Strip off the time, present the actual day or month name, include 'AM' or 'PM', use different separators... and on and on.  I've listed a few of my date formatting examples here.  Take a look, and let me know if you need something more.  Also, please remember that formatting or presentation of the data really should be done on the front end.  This stuff may be pretty, but it's not actually free.  Please keep in mind, there is overhead to formatting the date data like this.


SELECT CONVERT(VARCHAR(10),GETDATE(),1)   [MM/DD/YY]
SELECT CONVERT(VARCHAR(10),GETDATE(),4)   [MM.DD.YY]
SELECT CONVERT(VARCHAR(12),GETDATE(),111) [YYYY/MM/DD]
SELECT CONVERT(VARCHAR(12),GETDATE(),101) [MM/DD/YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),102) [YYYY.MM.DD]
SELECT CONVERT(VARCHAR(12),GETDATE(),103) [DD/MM/YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),104) [DD.MM.YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),105) [DD-MM-YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),113) [DD Mon YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),108) [hh:mm:ss]
SELECT CONVERT(VARCHAR(12),GETDATE(),110) [MM-DD-YYYY]
SELECT SUBSTRING(CONVERT(VARCHAR(11),DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),-1),113),4,8) [Mon YYYY]
SELECT LEFT(GETDATE(),11) [Mon D, YYYY]
SELECT CONVERT(CHAR(8),GETDATE(),112) [YYYYMMDD]
SELECT LEFT(CONVERT(CHAR(10),DATEADD(dd,-0,GETDATE()),101),5) [MM/DD]
SELECT CONVERT(DATETIME,CONVERT(CHAR(10),GETDATE(),120),120) [YYYY-MM-DD hh:mm:ss:ms ]
SELECT CONVERT(VARCHAR(10),GETDATE(),101)+ ''+SUBSTRING(CONVERT(VARCHAR(20),GETDATE(),100),13,7) [MM/DD/YYYY hh:mmPM]
SELECT DATENAME(MONTH,GETDATE()) AS [Month] 
SELECT DATENAME(m, DATEADD ( m , -1, GETDATE() )) [Last Month]
SELECT LEFT(getdate()-0, 11) [Today], LEFT(DATEADD(dy, 1, GETDATE()),11) [Tomorrow]
SELECT CONVERT(CHAR(5),GETDATE(),8) [hh:mm]
SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),100),13,9) [hh:mmPM]
SELECT CONVERT(VARCHAR(100),GETDATE(),114) [hh:mm:ss:ms ]

String searching

Sometimes you need to search all procedures, or views for object names.... maybe you're going to rename something, or maybe you have to alter a parameter size/type, and you need to determine everywhere it may be.  This is a handy little tool I put together ages ago to do just that.  It resides in my dba working database, which resides on all of my servers, and I have coded it to 'USE @dbname', so you can hit any database on each instance.  Check it out, let me know what you think.

/****** Object:  StoredProcedure [dbo].[usp_StringSearch]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[usp_StringSearch] (
 @dbname VARCHAR(25),
 @string VARCHAR(35),
 @debug BIT = 0
)
AS
SET NOCOUNT ON;
/* Allows me to traverse system objects for any reference to the given @string.  
Helpful if/when renaming or decommissioning objects.
EXEC dbo.usp_StringSearch @dbname='testdatabase',@string='%TBL_%'
*/


DECLARE @sql1 NVARCHAR(2000)
SET @sql1 = 'USE '+@dbname+'
SELECT DISTINCT OBJECT_NAME(ID) AS CallingObject
FROM sys.syscomments
WHERE [Text] LIKE '''+@string+'''
ORDER BY OBJECT_NAME(ID) ' 


IF(@debug = 1)
BEGIN
   PRINT(@sql1)
END
ELSE
BEGIN
   EXEC (@sql1)
END


SET NOCOUNT OFF;
GO


NOTE:  Use @debug if you want to be sure of the string you're passing, like this:
EXEC dbo.usp_StringSearch @dbname='testdatabase',@string='%TBL_%',@debug = 1


Output:

   USE testdatabase
   SELECT DISTINCT OBJECT_NAME(ID) AS CallingObject
   FROM sys.syscomments
   WHERE [Text] LIKE '%TBL_%'
   ORDER BY OBJECT_NAME(ID) 

Tuesday, January 4, 2011

New DATE Datatypes

Just a quick look at the new date datatypes:

CREATE TABLE dbo.NewDates  (
time time(7) NULL,
date date NULL,
smalldatetime smalldatetime NULL,
datetime datetime NULL,
datetime2 datetime2(7) NULL,
datetimeoffset datetimeoffset(7) NULL
)
GO

INSERT INTO dbo.NewDates (time,date,smalldatetime,datetime,datetime2,datetimeoffset)
VALUES (GETDATE(), GETDATE(), GETDATE(), GETDATE(), GETDATE(), GETDATE())
GO

SELECT * FROM dbo.NewDates

DROP TABLE dbo.NewDates


   time                                date                      smalldatetime
   20:12:17.1300000           2011-01-04            2011-01-04 20:12:00 


    datetime                               datetime2                                          datetimeoffset
    2011-01-04 20:12:17.130      2011-01-04 20:12:17.1300000            2011-01-04 20:12:17.1300000 +00:00

Sunday, January 2, 2011

Quick Check on Fragmentation

Just a quick check on sys.dm_db_index_physical_stats, returning the percentage of index fragmentation that is detected, for the given TABLENAME.


SELECT
    OBJECT_NAME(i.[object_id]) ObjectName,
    s.index_type_desc IndexType,
    i.name IndexName,
    s.partition_number [Partition#],
    ROUND(s.avg_fragmentation_in_percent, 2) [Fragmentation%]
FROM
    sys.dm_db_index_physical_stats
        (
        DB_ID(),
        OBJECT_ID('dbo.TABLENAME', 'U'), --<< CHANGE TO YOUR TABLE
        NULL,
        NULL,
        NULL
        ) AS s
    INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE
    s.index_id = i.index_id; 


DATETIME Functions

Having lived in the electronic trading industry for so long, I have become intimately familiar with our friend, DATETIME.  And DATE, TIME, DATETIME2, CURRENT_TIMESTAMP, DATEADD, DATEPART, DATEDIFF, DATENAME... I could go on and on.  


In my opinion, DATETIME2 was a wonderful thing.  Finally the precision of my date values are maintained!  There are many other DATE-specific changes in the new engine, too.  DATE and TIME, for example.  How many times are you stripping off one or the other?!  These changes are long overdue, and they really ease the date-related possibilities within SQL Server.


At this point, I'm just going to post tons of fun date stuff.  Different DATETIME functions, varying conversion methods, DO's and DONT's, even unexpected errors that you may receive, and why.  Let me know if you have any questions, and please feel free to send your own.  I would love to see them!





...in process

Compare Database Table Counts

Say you've got two databases, supposed to be identical..... use this method to compare table counts across both databases.  You will create a 'countcompare' working table in both databases, collect the counts explicitly, and then join the two working tables, to compare the counts.  You will run steps 1 and 2 in both databases, and then run step 3 to join the working tables, and compare the counts.  (Your servers must be linked.)


/* 1. drop/recreate countcompare (working table) */
  use databasename;
  if object_id('countcompare','u')>0
  drop table countcompare;
  create table countcompare (
  tablename varchar(50),
  recordcount int
);


/* 2. collect table counts */
  use databasename;
  declare @tabcnt int
  declare @printline char (60)
  select @tabcnt = count (*) from sysobjects where type = 'U'


  If @tabcnt != 0
  BEGIN
   insert dbo.countcompare(tablename,recordcount)
   select 'TABLE NAME'= convert (varchar (50), o.name), 
   ROWS=i.rows
   from sysobjects o, sysindexes i
   where o.type = 'U'
   and o.id = i.id
   and i.indid in (0,1)
   order by o.name
  END


  select @printline = '(' + convert (varchar(10), @tabcnt) +'    
  tables in ' + DB_NAME() + ')'


  print ''
  print @printline


/* 3. local db joined to otherServer.otherDB, return table counts side by side. */
  select a.tablename,a.recordcount,b.recordcount 
  from localDB.dbo.countcompare a 
  join otherserver.otherDB.dbo.countcompare b
   on a.tablename = b.tablename
  and a.recordcount <> b.recordcount 


NOTE:  You can pull that last line out, if you want to see all table counts, instead of just the ones that vary.

Counts Across Multiple Instances

In one of my previous roles, I was constantly needing to watch counts across multiple servers.  This is a clever method I put together to check table counts within static/definitional tables, on all servers, after daily manipulations occurred.  Three servers, one execution -- in this example, the table is 'Symbols':

/* Insert current lookup table counts, each instance. */
DECLARE @counts TABLE (
 ServerA char(13),ServerB char(13),ServerC char(13),
 TableName char(8),CountA int,CountB int,CountC int
)
INSERT @counts (ServerA,ServerB,ServerC,TableName,CountA,CountB,CountC)
SELECT a.ServerA,b.ServerB,ServerC,a.TableName,a.CountA,b.CountB,c.CountC
FROM
(
  SELECT @@SERVERNAME ServerA,'Symbols' TableName,COUNT(*) CountA 
  FROM SQLSERVER1.DatabaseName.dbo.Symbols
) a
INNER JOIN
(
  SELECT 'SQLSERVER2' ServerB,'Symbols' TableName,COUNT(*) CountB 
  FROM SQLSERVER2.DatabaseName.dbo.Symbols
) b
ON a.TableName = b.TableName
INNER JOIN
(
  SELECT 'SQLSERVER3' ServerC,'Symbols' TableName,COUNT(*) CountC 
  FROM SQLSERVER3.DatabaseName.dbo.Symbols
) c
ON a.TableName = c.TableName
ORDER BY a.TableName


/* Return table counts side by side. */
SELECT
LTRIM(RTRIM(TableName)) AS TableName,  
LTRIM(RTRIM(ServerA)) AS ServerA, 
LTRIM(RTRIM(CountA)) AS CountA,
LTRIM(RTRIM(ServerB)) AS ServerB, 
LTRIM(RTRIM(CountB)) AS CountB,
LTRIM(RTRIM(ServerC)) AS ServerC, 
LTRIM(RTRIM(CountC)) AS CountC
FROM @counts

Sample result:

  TableName ServerA           CountA   ServerB             CountB   ServerC              CountC
  Symbols       SQLSERVER1  35131      SQLSERVER2   35131      SQLSERVER3     35131

Clean up the white space.

Don't know how it happens, but sometimes we get blank, or empty characters within our data values.  I call this the 'white space'.  You can't really see it under normal conditions, but it definitely will hinder the accuracy of your data collections.  There's a very quick way to find AND fix it.

     --Find them
     SELECT '('+FieldName+')'
     FROM dbo.TableName
     WHERE some condition exists
  
     --Fix them (remove the whitespace)
     UPDATE dbo.TableName
     SET FieldName = LTRIM(RTRIM(FieldName))
     WHERE some condition exists 


If you've got a LOT of values with white space, you're definitely going to want to be careful with that update.  Let me know if that's the case.  Also, please understand that the above will only handle leading and trailing blank spaces.  (LTRIM = leading, RTRIM = trailing)  If you've got blank, or empty characters within the string values, you'll need to do something like this:


     UPDATE dbo.TableName

     SET FieldName = REPLACE(FieldName,' ','')

That will REPLACE any space in the targeted field with no space.  Always smart to look at the before and after side by side, before performing the update:

     SELECT FieldName, REPLACE(FieldName,' ','')

     FROM dbo.TableName



List all object permissions

In addition to the usp_CopyPerms, this is a clever CTE which just outputs all object permissions within the targeted database.  Good to use with build references, or even just check lists after builds, to ensure all of the necessary authentication is in place, before you go live.


WITH PERMScte AS
(
     SELECT USER_NAME(p.grantee_principal_id) AS principal_name,
     dp.principal_id,
     dp.type_desc AS principal_type_desc,
     p.class_desc,
     OBJECT_NAME(p.major_id) AS object_name,
     p.permission_name,
     p.state_desc AS permission_state_desc 
     FROM sys.database_permissions p
     INNER JOIN sys.database_principals dp
       ON p.grantee_principal_id = dp.principal_id
)
--users
SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM PERMScte p
WHERE principal_type_desc <> 'DATABASE_ROLE'
UNION
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM PERMScte p
RIGHT OUTER JOIN (
   SELECT role_principal_id, 
   dp.type_desc AS principal_type_desc,  
   member_principal_id,
   user_name(member_principal_id) AS member_principal_name,
   user_name(role_principal_id) AS role_name
   FROM sys.database_role_members rm
   INNER JOIN sys.database_principals dp
     ON rm.member_principal_id = dp.principal_id
) rm
ON rm.role_principal_id = p.principal_id
ORDER BY 1

Copy Object Permissions

How many times have you needed to compare object permissions between instances, because you've found the security definition isn't what you expected?  Or, maybe you have build a new instance, and you're just copying over permissions to ensure everything is in synch.  I've posted a quick procedure here which will return your permissions per object and user, along with the necessary privilege action (ie GRANT).  You can copy the output to another instance, and run it, as needed.


   IF OBJECT_ID('usp_CopyPerms','p')>0
   DROP PROC dbo.usp_CopyPerms
   GO
   CREATE PROC dbo.usp_CopyPerms (
    @dbname VARCHAR(35)
   )
   AS
   SET NOCOUNT ON;
   /* Outputs object permissions, per user, to be copied/run on another instance.
   EXEC dbo.usp_CopyPerms @dbname = 'DatabaseName'  */
   DECLARE @perms table (
     ObjectOwner VARCHAR(50) NULL,
     ObjectName VARCHAR(50) NULL,
     Grantee VARCHAR(50) NULL,
     Grantor  VARCHAR(50) NULL, 
     ProtectType VARCHAR(50) NULL,
     [Privilege] VARCHAR(50) NULL,
     [Column] VARCHAR(10))

   INSERT @perms
   EXEC ('EXEC ' + @DBName + '.dbo.sp_helprotect')

   SELECT LTRIM(RTRIM(ProtectType))+' '+LTRIM(RTRIM(Privilege))+' '+' ON '+[ObjectName]+' TO    '+Grantee+';
   ' FROM @perms
   WHERE ObjectOwner NOT IN('sys','.')
   AND ObjectName NOT LIKE '%OLD'
   AND ObjectName NOT LIKE '%TMP'
   ORDER BY ObjectName,Grantee

   SET NOCOUNT OFF;
   GO




EXAMPLE OUTPUT:
Deny Execute ON procedureName TO userName;   
Grant Execute ON procedureName TO userName;   
Grant Insert ON tableName TO userName;   
Grant Update ON tableName TO userName;