SP (Stored Procedure) Audit script for SQL Server 2005

I needed a script that showed the last change in time for the stored procedures in my database so I came up with the following. Certain of you may find it useful. Please comment if you have ideas for improvements.

–sp audit by Terry Grignon; for SQL Server 2005 and possibly newer versions
SELECT COUNT(DISTINCT modify_date) as [SP's changed],
datepart(yyyy,modify_date) as ModificationYear,
datepart(dy,modify_date) as ModificationDayOfYear,
min(modify_date) as [First Change],
max(modify_date) as [Last Change]
FROM sys.sql_modules
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id
AND TYPE = ‘P’
group by datepart(yyyy,modify_date), datepart(dy,modify_date)
order by datepart(yyyy,modify_date), datepart(dy,modify_date);

SELECT name, create_date, datepart(dy,create_date) as CreatedDayOfYear,
modify_date, datepart(dy,modify_date) as ModificationDayOfYear
FROM sys.sql_modules
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id
AND TYPE = ‘P’
order by datepart(yyyy,modify_date) asc,
datepart(dy,modify_date) asc, name;

Query to return table rowcounts & identity values in SQL Server

Sometimes when you are doing administrative or investigative work in your database you need to get an up-to-date idea of the the row count and Identity key values (for those tables having them). I created the following script to this quickly for whichever database you are currently using in SQL Server.
It doesn’t work very well for schema partitioned tables like those you’d find in Microsoft’s distributed example database ‘Adventureworks’ but for databases with only DBO tables it should work fine. As always, feel free to use/modify this script to your heart’s content.

/*
-run the following to get a row count and the max primary key for an identity column of all DBO user tables with Identity keys
-also run the same without the max primary key for all DBO user tables without Identity keys
-doesn’t work well with databases with non DBO schemas i.e.: AdventureWorks will not show very many rows
*/

declare @tbltmpStats table (
Identity_Tables int identity (1, 1) not null ,
Table_Name nvarchar(200),
Num_Rows int,
Identity_Max int)
declare @objectid int;
declare @objectname nvarchar(200);
declare @sql1 nvarchar(4000)
declare @objectcursor cursor

set @objectcursor=cursor for select o.object_id from sys.objects o
where type=’u’
and objectproperty(o.object_id, N’TableHasIdentity’)=1
and o.schema_id = 1
order by o.name
open @objectcursor
fetch next from @objectcursor into @objectid
while (@@fetch_status=0) begin
select @objectname=name from sys.objects where object_id=@objectid
set @sql1 = ’select ”’ + cast(@objectname as nvarchar(200)) + ”’, count(*), max(identitycol) from ‘ + @objectname
insert into @tbltmpstats (Table_Name, Num_Rows, Identity_Max)
exec (@sql1)
fetch next from @objectcursor into @objectid
end
close @objectcursor
select * from @tbltmpstats

declare @tbltmpStats2 table (
NonIdentity_Tables int identity (1, 1) not null ,
Table_Name nvarchar(200),
Num_Rows int
)
set @objectcursor=cursor for select o.object_id from sys.objects o
where type=’u’
and objectproperty(o.object_id, N’TableHasIdentity’)=0
and o.schema_id = 1
order by o.name
open @objectcursor
fetch next from @objectcursor into @objectid
while (@@fetch_status=0) begin
select @objectname=name from sys.objects where object_id=@objectid
set @sql1 = ’select ”’ + cast(@objectname as nvarchar(200)) + ”’, count(*) from ‘ + @objectname
insert into @tbltmpstats2 (Table_Name, Num_Rows)
exec (@sql1)
fetch next from @objectcursor into @objectid
end
close @objectcursor
select * from @tbltmpstats2
deallocate @objectcursor

Using a ‘zero’ row in SQL Server tables

At first I rebelled against the very idea of doing this but I was eventually convinced and now all my tables have zero rows. This doesn’t mean there are no rows in the tables, this means that whenever I create a new table I insert a special row with a primary key of zero and default values in the other fields. So my table create scripts look something like this:

create table dbo.tblmyvalue (
myvalueid_pk int identity (0, 1) not null ,
strmyvalue nvarchar (200) COLLATE SQL_Latin1_General_CP1_CS_AS not null constraint def_tblmyvalue_strmyvalue default ‘~na~’ ,
constraint tblmyvalue_myvalueid_pk primary key clustered(myvalueid_pk)
) on primary
go
set identity_insert tblmyvalue on
insert into tblmyvalue (myvalueid_pk, strmyvalue) values (0, ‘~na~’)
set identity_insert tblmyvalue off

Why?
The simple answer is “it’s easy”. Whenever I have another table refer to this new table, I can always have zero values in the foreign keys for it. I don’t have to worry about relational integrity: I can use 0 since there will always be a zero row that can be referred to. This may seem the shortcut of someone who likes to avoid referential integrity but it actually has many more benefits and efficiencies than a precisely crafted database that takes forever to set up without this in place. In fact, having lived with it for a while I consider this approach very ‘relational’, perhaps even ‘reverential’ (praise Cobb) since everything is linked! Here is a list of some of these benefits:

1. all data rows from 1 onward have meaningful data that isn’t there merely as a placeholder for another table
2. a table with its own ordering system (an internal integer field with a foreign key constraint pointing to its own primary key) can be easily created and maintained i.e.: those that point to zero are the first in the list and those that point to it, follow
3. default constraints on a table field can be used for error trapping instead of imposing referential integrity; all inserts simply require 0 for foreign key fields
4. even tables that aren’t pointed to by foreign keys may eventually be and Holy Relational Tables, Batman, me and my zero rows will be ready

I modified a script (since updated) by Tatsu I found on SQLServerCentral to automate the data entry of the zero rows. I find it also useful to use if you forget the fields in a particular table. My version follows:

/*
**modified 2005/07/26 for SQL Server 2005 by Terry Grignon
**change row “set @TableName = ‘tblyourtable’” to whatever table you want to ‘zero’
Credits:
Tatsu & Bob Wiechman
*/
declare @TableName sysname
declare @IdentityInsert int
declare @ColName sysname
declare @ColType tinyint
declare @DebugMode bit
declare @ColList nvarchar(4000)
declare @ValList nvarchar(4000)
declare @NewList nvarchar(4000)
declare @SQL1 nvarchar(4000)
declare @SQL2 nchar(10)
declare @SQL3 nchar(1000)

set @TableName = ‘tblyourtable’
set @IdentityInsert = 1 — set to 0 if you DON’T want IDENTITY_INSERT turned on
set @DebugMode = 1 — set to 1 if you only want a script
set @ColList = ”
set @ValList = ”
set @SQL1 = ‘insert into ‘ + @TableName + ‘ (‘
set @SQL2 = ‘) values (‘
set @SQL3 = ‘)’
–set @SQL3 = ‘)”, ”””null”””, ”null”) from ‘ + @TableName
–print @sql1 + @sql2 + ‘)’
print ‘cut below’

declare csrColumns cursor local fast_forward for
select c.name, c.system_type_id –xtype
from sys.columns c –select * from sys.columns where name = ‘vbarpassword’
inner join sys.objects o –select * from sys.objects
on o.object_id = c.object_id
where o.name = @TableName
and o.type in (‘U’, ‘S’)
order by Column_id

open csrColumns
fetch next from csrColumns into @ColName, @ColType

while @@fetch_status = 0
begin
– uncomment line below to see the numeric types
– print @ColName + ‘ ‘ + convert (varchar (10), @ColType)
set @ColList = @ColList + ‘ ‘ + @ColName
if @ColType in (173, 62, 60, 108, 59, 122)
set @ValList = @ValList + ‘~’
if @ColType in (165)
set @ValList = @ValList + ‘convert(varbinary,0)~’
– set @ValList = @ValList + ‘ ”+convert(varchar(200),’ + @ColName + ‘)+”’
– uid and string types
else if @ColType in (175, 239, 48, 52, 56, 36, 167)
set @ValList = @ValList + ‘0~’
else if @ColType in (106)
set @ValList = @ValList + ‘0.0~’
else if @ColType in (36, 61, 104, 231)
set @ValList = @ValList + ‘default~’
– set @ValList = @ValList + ‘ ”””+isnull(‘ + @ColName + ‘,”null”)+”””’
– dates (does not support nulls yet)
else if @ColType in (58)
set @ValList = @ValList + ‘~’
– set @ValList = @ValList + ‘ ”””+convert(varchar(200),’ + @ColName + ‘)+”””’
fetch next from csrColumns into @ColName, @ColType
end

close csrColumns
deallocate csrColumns

set @ValList =@ValList + ‘~’

set @ColList = replace(ltrim(@ColList), ‘ ‘, ‘, ‘)
set @ValList = replace(ltrim(@ValList), ‘~~’, ”)
set @ValList = replace(ltrim(@ValList), ‘~’, ‘, ‘)

if @IdentityInsert = 1
print ’set identity_insert ‘ + @TableName + ‘ on’

if @DebugMode = 1
Begin
set @NewList = @SQL1 + @ColList + @SQL2 + @ValList + @SQL3
set @NewList = rtrim(@NewList)
print @NewList
end
else
exec (@SQL1 + @ColList + @SQL2 + @ValList + @SQL3)

if @IdentityInsert = 1
print ’set identity_insert ‘ + @TableName + ‘ off’

print ‘cut before’

Using Excel to generate Inserts for SQL Server

Much of my day job involves database design, maintenance and manipulation. I use SQL scripts to quickly build my databases from scratch. In order to test them I need piles of data and find it is often too much work to generate all those inserts manually or figure out sqlcmd or osql code to import bulk data.
The best programmers, as many say, are lazy and the quickest way I’ve found to generate the inserts is to use Excel. If any of the following isn’t clear please attach a comment and I’ll be glad to explain in further detail.
Doing everything with scripts is certainly doable but the changes and administration can be a real pain. So I use Excel formulae to generate the data insert scripts for me. The first step is to lay my data out in columns in a spreadsheet. A quick way to do this using SQL Server Management Studio is to choose the option that allows the headers to be exported along with the data and then just copy the current data from a database table into a spreadsheet using the clipboard. Skip a column (or use it for notes) and then type something like the following formula in it:
="insert into tblyourtablename (yourkeyID_pk, intmine, strval) values ("&A4&", "&B4&", N'"&C4&"')"
Now you’ve got your insert statement for a table with your primary key (PK), an integer and a unicode string. Now all you need to do is to use Excel’s fill tools to copy data and the formulae down the rows and then just copy the whole column and insert into a SQL script. Including PK’s requires statements like
set identity_insert tblyourtablename on
and
set identity_insert tblyourtablename off
to bracket your insert statements. But if you want to avoid that hassle, you can skip the PK’s as I do in this statement:
="insert into tblyourtablename (intmine, strval) values ("&B4&", N'"&C4&"')"
Of course, if you skip the PK’s then you need to keep any foreign keys you may be using straight. How do you do that you might ask? It’s all doable. You simply arrange the order of things if you like to keep the PK’s. So your data insert script would have lookup table inserts at the top, and then those for tables with foreign keys later.
Or you get fancy and make your scripts relative (you can use your inserts regardless of whether there’s data already present or no) by using variables. In this way if you have a set of tables that rely on each other you could chain the insert statements together with semi colons and local variables for the foreign keys. Here’s an example:
declare @intpane int;
="insert into tblyourtablename (intmine, strval) values ("&B4&", N'"&C4&"'); set @intpane = scope_identity(); INSERT INTO tblpane (nameid_fk,strtext,bitmine,vbarmine) VALUES (@intpane ,N'"&D8&"' ,0 ,convert(varbinary,''));"
Bear in mind that you must copy the above into your SQL script by selecting the entire block if you have formulae in multiple columns.
There are a few tricks to keep in mind.

• The text designator in SQL is the ‘ whereas it is the ” (double quote) in Excel.
• Sometimes you need to insert text that contains a ‘ and that can really mess your code up so you need to put two ‘ together in that data column. For example the name O’Brien needs to be entered in the data column in Excel as O”Brien (this is easily down with a simple search and replace).
• Remember to enter “”"” if you need to print a ” with Excel.
• Date/time fields are inserted like text in SQL (they use the ‘ too, like in ‘1956-09-01 13:24:16.000′, I find it easier to use 000 for the milliseconds because in SQL they go up by 3.33 and so always round to end in 0, 3 or 7).