Super Quick Table Meta Data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jacques Bosch » Super Quick Table Meta Data

38 posts, Page 2 of 4

««1 2 3 4 »»»

Super Quick Table Meta Data

Rate Topic

Display Mode

Topic Options

Author

Message

Jacques-897195

Posted Monday, March 23, 2009 7:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:46 PM
Points: 7, Visits: 69

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.

Post #681431

Tom Van Harpen

Posted Monday, March 23, 2009 8:19 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:15 AM
Points: 386, Visits: 901

Hey GSquared, thanks for that post. - Haven't moved to '08 yet.

Post #681466

Adam Seniuk

Posted Monday, March 23, 2009 8:23 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 478, Visits: 725

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. :D

Over 11yrs in IT and 9yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012
- Oracle 8/9/10
- MySQL 4/5

Post #681470

randal.schmidt

Posted Monday, March 23, 2009 8:33 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, October 26, 2012 9:46 AM
Points: 33, Visits: 116

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".

Post #681484

Adam Seniuk

Posted Monday, March 23, 2009 8:38 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 478, Visits: 725

Please see the above attached 2005 friendly script...

Over 11yrs in IT and 9yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012
- Oracle 8/9/10
- MySQL 4/5

Post #681490

andrewa

Posted Monday, March 23, 2009 9:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 26, 2009 6:13 AM
Points: 1, Visits: 14

ola

thought i saw that code somewhere, was thinking of until ... your name dawned on me BigGrin

Post #681564

Rich Holt

Posted Monday, March 23, 2009 10:17 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:44 AM
Points: 91, Visits: 94

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. Smile

Post #681618

Tao Klerks

Posted Monday, March 23, 2009 10:40 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, April 14, 2013 8:55 AM
Points: 1,383, Visits: 1,212

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.

Post #681636

crapper_mail

Posted Monday, March 23, 2009 12:18 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 7:29 AM
Points: 3, Visits: 30

But surprisingly, not triggers.

Post #681717

crapper_mail

Posted Monday, March 23, 2009 12:20 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 7:29 AM
Points: 3, Visits: 30

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.

Post #681720

« Prev Topic | Next Topic »

38 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions