Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Super Quick Table Meta Data


Super Quick Table Meta Data

Author
Message
SQLAJ
SQLAJ
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 410
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
Arko Oige
Arko Oige
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 199
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
Jacques-897195
Jacques-897195
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 74
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.
Attachments
TableInfo.sql.txt (9 views, 9.00 KB)
cox.ian
cox.ian
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 4
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 = '';


Rich Holt
Rich Holt
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 194
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
Attachments
TableInfo.r2.sql.txt (14 views, 9.00 KB)
Santiago E. Perez
Santiago E. Perez
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 71
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!
Santiago E. Perez
Santiago E. Perez
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 71
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.
Attachments
TableInfo_SQL2K.txt (2 views, 10.00 KB)
Jacques-897195
Jacques-897195
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 74
Hi Santiago.

Very glad it is of use to you. I still use it almost every day myself. Smile

Thanx for the 2K work. I hope I never need it! Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search