Tracking Database DDL Changes with SQLVer

  • Comments posted to this topic are about the item Tracking Database DDL Changes with SQLVer

  • Hi, all. One other neat feature that SQLVer provides is a quick and easy way to search through stored procedure and function source code for a specified string. For example, you can do this:

    EXEC sqlver.find 'abc'

    This will return a resultset that lists the schema, the object name, and the relevant portion of the source for all programability objects that contain the string 'abc'.

    Sometimes I will also create a synonym that points to this stored procedure, so that I can simply call it like this:

    find 'abc'

    To create this synonym, I would just execute this:

    CREATE SYNONYM dbo.find FOR sqlver.sputilFindInCode

    There's more it does too, such as providing a simple debug logging framework.

    I'm not trying to oversell, but would be pleased to discuss some details with anyone interested.

  • I have just installed this and given it a try. So far so good; nice and smooth and easy to use. However I was confused for a moment when I made a table alter to test it.

    I used SSMS to edit the table rather than running an alter (to see what data I got back). Because table alters are done by recreating the table you don't get the history by table name. SSMS renames the table as Tmp_[TableName] and then creates the new table and moves the data.

    Example to explain my ramblings:

    1. A table called [dbo].[testSqlVer] exists with a column called [TestColumn]

    2. You use ssms to change the column name to [TetsColumnRenamed]

    3. This enters two rows into the schema log:

    CREATE_TABLETmp_testSqlVer

    DROP_TABLEtestSqlVer

    It doesn't actually store the change (the alter). My suggestion is that the sqlver.ver proc checks for corresponding Tmp_ objects when you search for a table; at least that was you get to see the changes (by looking at the scripts).

    Granted no one should be using ssms designer to make a change to a live database; but as you say the perfect world is not the real world!

    Cheers,

    James

  • Thanks for sharing, looks very promissing.

    "Written for Microsoft SQL 2008 R2 or later, but should run on MSSQL 2005.".

    No, spShowSlowQueries() will not compile in 2008 because sys.dm_exec_query_stats() was altered in 2008 R2. All the rest compiles with no problems.

  • I tried to install SQLVer in a datatabase with an account in the dbo group (i.e. I'm the owner of that database). Here's what I got:

    .Net SqlClient Data Provider: Msg 207, Level 16, State 1, Procedure spShowSlowQueries, Line 25

    Invalid column name 'total_rows'.

    .Net SqlClient Data Provider: Msg 207, Level 16, State 1, Procedure spShowSlowQueries, Line 26

    Invalid column name 'last_rows'.

    .Net SqlClient Data Provider: Msg 15517, Level 16, State 1, Procedure spBuildManifest, Line 6

    Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

  • Ah, good points: Yes, SQLVer at present doesn't track renames: that is something I need to fix. Originally (in SQL 2005) renames did not fire a DDL trigger, so there was no way for me to track renames...and I gave up.

    Now, since SQL 2008, a rename trigger should work, though I haven't implemented that. I'll take a look at making that change.

    Furthermore, I think SSMS does disable DDL triggers for at least certain alters. In that case...of course a DDL trigger obviously can't detect the alter.

    SQLVer is definitely subject to the limitations of DDL triggers.

  • If you run into errors when creating spShowSlowQueries, please try commenting out the creation of spShowSlowQueries.

    While useful, that procedure isn't really needed for the core version tracking functionality.

  • David Rueter (1/22/2015)


    Ah, good points: Yes, SQLVer at present doesn't track renames: that is something I need to fix. Originally (in SQL 2005) renames did not fire a DDL trigger, so there was no way for me to track renames...and I gave up.

    Now, since SQL 2008, a rename trigger should work, though I haven't implemented that. I'll take a look at making that change.

    Furthermore, I think SSMS does disable DDL triggers for at least certain alters. In that case...of course a DDL trigger obviously can't detect the alter.

    SQLVer is definitely subject to the limitations of DDL triggers.

    A member of my team got really confused when he altered a proc on a dev box earlier and it came up with:

    "Changes to xyz successfully logged by SQLVer". Just for the confusion that followed I give you 10 points!

  • 🙂 You can of course remove the PRINT statement if you want SQlVer to run in "stealth" mode.

  • Haven't done a full test yet, but instead of disabling [spShowSlowQueries], I think we can fake the two columns that are not available for versions before 2008 R2. spShowSlowQueries is a very nice feature to have, after all.

    e.g.

    null as total_rows, -- s.total_rows, --// available in 2008 R2 +

    null as last_rows, -- s.last_rows, --// available in 2008 R2 +

  • Great article. We have been using DDL Loging for quite some time, with different iterations of T-SQL scripts.

    And, there is a way to circumvent the PRINT limitation on length and allow you to print the whole code:

    CREATE PROCEDURE [dbo].[sp_printl]

    @printstring as VARCHAR(MAX)

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @cr CHAR(1),

    @lf CHAR(1),

    @string AS VARCHAR(MAX),

    @len INT,

    @cr_index INT,

    @lf_index INT,

    @crlf_index INT,

    @has_cr_and_lf BIT,

    @left NVARCHAR(4000),

    @reverse NVARCHAR(4000)

    SET @string = LTRIM(RTRIM( @printstring ))

    SET @cr = CHAR(13)

    SET @lf = CHAR(10)

    SET @len = 4000

    WHILE ( LEN( @string ) > @len )

    BEGIN

    SET @left = LEFT( @string, @len )

    SET @reverse = REVERSE( @left )

    SET @cr_index = @len - CHARINDEX( @cr, @reverse ) + 1

    SET @lf_index = @len - CHARINDEX( @lf, @reverse ) + 1

    SET @crlf_index = CASE WHEN @cr_index < @lf_index THEN @cr_index ELSE @lf_index END

    SET @has_cr_and_lf = CASE WHEN @cr_index < @len AND @lf_index < @len THEN 1 ELSE 0 END

    PRINT LEFT( @string, @crlf_index - 1 )

    SET @string = RIGHT( @string, LEN( @string ) - @crlf_index - @has_cr_and_lf )

    END

    PRINT @string

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • Richard, thanks for the comment. Just to clarify, sqlver.sputilPrintString does in fact print the entire string. The issue is that as does the code you posted, multiple PRINT statements are executed in a loop to output "chunks" of the string. Each call to PRINT outputs a CR + LF (newline), and as far as I know there is no way to suppress this newline.

    This creates a problem if the end of a chuck falls in the middle of a line, because you end up with a line break where one does not belong.

    My crude solution was to just output a ~ to alert you to the fact that an extraneous newline had been added.

    Your approach of actually inspecting for a line break in the string and trying to print a chunk that excludes the original line break is more elegant, but I think this will still run into trouble if there is no line break in a long string that exceeds the maximum length of a chunk.

    I'll take a closer look at your approach, but every time I have looked at this in the past I have failed to find a 100% reliable way to PRINT long strings without the risk of extraneous line breaks being inserted. That's why I opted to just tag the line breaks with a ~.

    But in any case, thanks for sharing your code with us!

  • David, I believe the code I posted does not have that problem. I routinely use it when I need to recover some code that was updated.

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • Great! If it is OK with you, I'll see about using your approach in SQLVer: I'm happy to have all the help and improvements I can get.

    Thanks again.

  • To tripple-check, I just tested in on a 75K character long SP and it printed out just fine. If you look at the code, it dynamically makes sure a CR+LF is not in the middle of code

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply