March 26, 2009 at 2:15 pm
Thanks for the article. Nice little snippet to add to my bag of tricks.
I usually create an ERD before creating the tables and keep that updated as things change.
But this has uses on its own.
Thanks go out to GSquared for doing the 2005 version.
______________________________
AJ Mendo | @SQLAJ
April 8, 2009 at 2:03 am
In addition, if there is more than one index on a column, it will show the number of indexes in brackets at the start. For example, the IX column might read '(2) c, unique, desc (disable)'
when ix.COLIMN_NAME is not null then
case
when ix.type_desc = 'CLUSTERED' then 'c'
when ix.type_desc = 'NONCLUSTERED' then 'nc'
else ix.type_desc
end
...
else
@Empty
This functionality seems not to be working as I have 3 indexes on the same column but still see only “nc, desc” in IX column.
Even the code extract shows that column IX always starts with the index type not the count.
Am I having an old version of the code or is this functionality to be added in future?
Regards,
Arko
April 8, 2009 at 3:26 am
Hi Arko Oige.
No that is incorrect. I have no idea how that version of the code got there but it doesn't include the index count. It should look like the below.
-- If there is more than 1 index on a column, indicate the number of indexes at start.
case
when ix.IndexCount > 1 then
'(' + CAST (ix.IndexCount as varchar) + ') '
else
@Empty
end
+ case
when ix.type_desc = 'CLUSTERED' then 'c'
when ix.type_desc = 'NONCLUSTERED' then 'nc'
else ix.type_desc
end
Also note that GSquared's 2005 version has another problem with showing up indexes correctly, but I haven't taken a look at that.
Below is reattached the SQL 2008 version including the index count.
Note, index count will only show up if there is more than 1 index.
Let me know if it works for you.
April 15, 2009 at 4:10 am
Nice article. I have been working on a table based code generator and this may actually allow me to speed things up a little.
Code does need tweaking for 2005. The setup section should read something like below to pass the execution.
declare @DBName varchar (20)
declare @TableSchema varchar (20)
set @DBName = PARSENAME (@TableName, 3)
set @TableSchema = PARSENAME (@TableName, 2)
set @TableName = PARSENAME (@TableName, 1)
if @DBName is not null
and @DBName != DB_NAME()
begin
print 'Cannot run this on DB ''' + @DBName + '''. Must be run on current DB.'
return;
end
-- Set up some values for displaying the results.
declare @Y varchar (10)
set @Y = ' y'; -- Spacing is for nicer look.
declare @Empty varchar (1)
set @Empty = '';
April 15, 2009 at 11:57 am
Nice script! Thanks for sharing, Jacques!
I made some mostly syntactical changes:
- can't assign default values to local variables in SQL 2005
- character case differences will break the code in BINARY (or case sensitive -?) collations
August 25, 2011 at 8:41 am
Just found this article and am thrilled because I am working on a new contract with a crazy DB system. This is immensely valuable except I need it for SQL2K. Has anyone tackled this yet? I am currently trying to re-work it for SQL2K but my knowledge of system tables is limited so it's taking me a while to inch along. If I get it to work I will share unless someone posts a SQL2K version first.
Thanks again Jacques!
August 25, 2011 at 11:32 am
OK took a stab at making it SQL2K compatible and added Meta Data Description as an extra column. Not perfect but so far it seems like it works on most of my tables.
August 25, 2011 at 1:34 pm
Hi Santiago.
Very glad it is of use to you. I still use it almost every day myself. 🙂
Thanx for the 2K work. I hope I never need it! 🙂
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply