Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Super Quick Table Meta Data Expand / Collapse
Author
Message
Posted Thursday, March 26, 2009 2:15 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 11, 2014 5:57 AM
Points: 85, Visits: 401
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
Post #684575
Posted Wednesday, April 8, 2009 2:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 2:33 AM
Points: 13, Visits: 176
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
Post #692829
Posted Wednesday, April 8, 2009 3:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:33 PM
Points: 7, 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.


  Post Attachments 
TableInfo.sql.txt (7 views, 9.79 KB)
Post #692874
Posted Wednesday, April 15, 2009 4:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 5:56 AM
Points: 8, 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 = '';

Post #697362
Posted Wednesday, April 15, 2009 11:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:00 PM
Points: 94, Visits: 148
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




  Post Attachments 
TableInfo.r2.sql.txt (11 views, 9.93 KB)
Post #697789
Posted Thursday, August 25, 2011 8:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 3, 2011 10:55 AM
Points: 19, 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!
Post #1165408
Posted Thursday, August 25, 2011 11:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 3, 2011 10:55 AM
Points: 19, 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.


  Post Attachments 
TableInfo_SQL2K.txt (1 view, 10.67 KB)
Post #1165615
Posted Thursday, August 25, 2011 1:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:33 PM
Points: 7, Visits: 74
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! :)
Post #1165705
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse