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
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
Thanx, SSCertifiable Squared, for converting it to be SQL 2K5 friendly.
Yes, I used some features such as the variable initialization that are features of 2K8, as that is all that I'm using these days.

I'll comment on the other points such as sp_help later.

Thanx much.
Tom Van Harpen
Tom Van Harpen
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1056
Hey GSquared, thanks for that post. - Haven't moved to '08 yet.
Adam Seniuk
Adam Seniuk
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 1038
You know a feature that would be neat to add, if you left the table empty or put in a %, and then typed in a column name... Then return all of the tables that hold that specific column name.

Just thinking out loud. BigGrin


Over 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
randal.schmidt
randal.schmidt
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 120
I had the same issue with SQl Server 2005. Could not buikd the sp.

Msg 139, Level 15, State 1, Procedure TableInfo, Line 0
Cannot assign a default value to a local variable.
Msg 139, Level 15, State 1, Procedure TableInfo, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure TableInfo, Line 17
Must declare the scalar variable "@DBName".
Msg 137, Level 15, State 2, Procedure TableInfo, Line 20
Must declare the scalar variable "@DBName".
Msg 139, Level 15, State 1, Procedure TableInfo, Line 0
Cannot assign a default value to a local variable.
Msg 139, Level 15, State 1, Procedure TableInfo, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure TableInfo, Line 65
Must declare the scalar variable "@TableSchema".
Msg 102, Level 15, State 1, Procedure TableInfo, Line 86
Incorrect syntax near ','.
Msg 137, Level 15, State 2, Procedure TableInfo, Line 116
Must declare the scalar variable "@TableSchema".
Msg 137, Level 15, State 2, Procedure TableInfo, Line 135
Must declare the scalar variable "@Empty".
Msg 137, Level 15, State 2, Procedure TableInfo, Line 316
Must declare the scalar variable "@TableSchema".
Msg 137, Level 15, State 2, Procedure TableInfo, Line 335
Must declare the scalar variable "@Y".
Adam Seniuk
Adam Seniuk
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 1038
Please see the above attached 2005 friendly script...


Over 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
andrewa
andrewa
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 14
ola

thought i saw that code somewhere, was thinking of until ... your name dawned on me :-D
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
By default, we use a BINARY collation on my team. The TableInfo code has a few character case problems, which were easy to find and fix. Same goes for the assignment of default values to local variables; easy enough to fix in the script.

Don't make the assumption that you can prefix the proc name with "sp_" , create it in master, and have it work in any User database. It won't necessarily generate any errors, but it also won't generate any output.

Thanks for the example, it presents some food for thought. :-)
Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1249
Hi Rich,

This seems to scare some people but it works very nicely in my experience:

By setting stored procedures as being a "system object", you can create them in the master database, prefix them with "sp_", and have them run in any database "natively".

The proc for doing this is "sp_ms_marksystemobject" - it works in 2000 and 2005, I have not tested in 2008.

Hope this helps!
Tao

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
crapper_mail
crapper_mail
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 44
But surprisingly, not triggers.
crapper_mail
crapper_mail
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 44
Just change the lines that read:

declare @DBName varchar (20) = PARSENAME (@TableName, 3)

to

declare @DBName varchar (20)
set @DBName = PARSENAME (@TableName, 3)

SQLServer 2005 just requires 2 rows instead of 1 row.
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