January 21, 2015 at 10:22 pm
Comments posted to this topic are about the item Tracking Database DDL Changes with SQLVer
January 21, 2015 at 10:30 pm
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.
January 22, 2015 at 2:25 am
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
January 22, 2015 at 2:35 am
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.
January 22, 2015 at 6:38 am
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.
January 22, 2015 at 8:11 am
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.
January 22, 2015 at 8:14 am
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.
January 22, 2015 at 8:19 am
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!
January 22, 2015 at 8:25 am
🙂 You can of course remove the PRINT statement if you want SQlVer to run in "stealth" mode.
January 22, 2015 at 8:29 am
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 +
January 22, 2015 at 8:42 am
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
January 22, 2015 at 9:21 am
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!
January 22, 2015 at 9:27 am
January 22, 2015 at 9:29 am
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.
January 22, 2015 at 9:35 am
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply