Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

NameSQLinConfioIgnite

By Thomas Keller,

This will name Ignite SQL hashes that have not been named yet, based on the object (or batch) they are within.
Runs on MS-SQL2005+ (uses VARCHAR(MAX) to concatenate SQL sections).
Run this script in ignite_repository database (but if you do not, it will just error and exit without doing anything).
It works best if you have a shorthand code for the object (or batch), with no spaces in it.
Place a comment like this near the beginning of the batch (at the end of the CREATE line of the object):
CREATE PROCEDURE [schema].[procname] -- Ignite=ShortObjCode VersionCode
Place a comment like this within or directly after each statement: -- Ignite=ShortObjCode ShortStmtCode
The hash for the statement will then be named "ShortObjCode ShortStmtCode VersionCode" as long as
 Ignite stored the entire object script (or batch), which it might not if it was very long.
But it will work without such comments, by using the first 18 chars of the Object name.
YYMMDD and the last 4 digits of the hash are used, if necessary, to enforce uniqueness.
The last result set will be the SQL Text that still could not be named (not in an object, and no such comment).
If you are unable to inject such comments into the SQL, you may be able to see a pattern in the SQL
 which would enable you to synthesize an 18-char "object name" yourself. Search for "synthesize" in the script to see where.
The last column of both result sets is a link to the Historical Chart for that SQL, so you can check its usage.
 (That link is built assuming Ignite is running on localhost, but you can just replace localhost in the script.)
Free to use, developed by tomkellerconsulting.com 14 Sep 2012.
Submitted to Confio Support 13 March 2013. Submitted to sqlservercentral.com 25 Jan 2014.
Updated 28 Feb 2014 to use LEFT(NEWID(), 4) -- random
 instead of RIGHT(CONVERT(VARCHAR(20), TS.H), 4) -- last 4 digits of the hash
 to avoid duplicates in large sets of un-named hashes. Also added "double-check not duplicate" to final insert.
Also set @DOIT = 0 by default so no changes will be made unless requested, and select Note if no names inserted.

Total article views: 575 | Views in the last 30 days: 2
 
Related Articles
BLOG

Commenting TSQL Scripts

If you have ever searched for “commenting in TSQL” scripts, I am sure that you’ve found hundreds or ...

FORUM

Scripting Objects Including Permissions

Scripting Objects Including Permissions

FORUM

SSIS script object

Script object Design Script does not open up script editor

FORUM

Generating Scripts for Database Objects

Creating Seperate Scripts Per Object

FORUM

Scripting objects in SSMS (2008)

Scripted objects are being wrapped in dynamic SQL

Tags
confio    
fuzzy match    
ignite    
performance tuning    
search    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones