Thanks for the cool script Chris,

I've been looking to build some sort of auditing system for my company for a while now but haven't really thought much about how to easily manage it.

In playing with the script I found a three small issues I thought I would bring to you.

First off you might want to declare the @SQLColumn and @SQLColumnSelect variables a bit larger. In testing i found that 1000 characters was not enough for some of the tables in my companies database. create statements were getting cut off.

We're running SS2k5 Enterprise so i just declared all variables as varchar(max) just so i didn't have to worry. not the best solution but it works 🙂

the second issue i found a small bug in that it does not add the column sizes to nchar and nvarchar variables... around line 106 here is the code and what you can add to fix this:

current code:

IF ((@COLUMNTYPE='varchar') or (@COLUMNTYPE='char'))

new code:

IF ((@COLUMNTYPE='varchar') or (@COLUMNTYPE='char') or (@COLUMNTYPE='nvarchar') or (@COLUMNTYPE='nchar')

that'll make sure the lengths get attached for nvarchar and nchar.

finally the create table function is a bit limiting in support for datatypes such as decimals, binary, varbinary so i made some small modifications so it properly creates those variables and adds their limits.

1) change line 68 from

DECLARE @COLUMNSIZE INT

to

DECLARE @COLUMNSIZE varchar(100)

2) change the @COLUMNSIZE select on line 77 to:

@COLUMNSIZE=CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1

THEN cast((clmns.max_length/2) as varchar(100))

WHEN baset.name in (N'decimal')

THEN cast(clmns.precision as varchar(4)) + ',' + cast(clmns.scale as varchar(4))

ELSE cast(clmns.max_length as varchar(100))

END

3) change the if statement on line 106 to:

IF ((@COLUMNTYPE='varchar') or (@COLUMNTYPE='char') or (@COLUMNTYPE='nvarchar') or (@COLUMNTYPE='nchar') or (@COLUMNTYPE='binary') or (@COLUMNTYPE='varbinary') or (@COLUMNTYPE='decimal') )

BEGIN

SET @SQLColumn=@SQLColumn+'('+CASE WHEN ltrim(@COLUMNSIZE) = '-1' then 'MAX' ELSE ltrim(@COLUMNSIZE) END

+') '

END

making those 3 changes will allow it to properly generate the maxes for binary types as well as do a precision,scale for decimals like "[UserScore] [decimal] (6,2) NULL, "

Otherwise you have one sweet script and thanks for sharing!

For my use i'm making a few other changes that will work better for my particual needs. I'm removing the ability to specify the audit database and table names since there is only one for the company and i want to make sure they all go to the right database and follow the correct naming scheme.

Thanks! 🙂