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 Monday, March 23, 2009 7:48 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
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
Posted Monday, March 23, 2009 8:19 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 9:33 AM
Points: 407, Visits: 968
Hey GSquared, thanks for that post. - Haven't moved to '08 yet.
Post #681466
Posted Monday, March 23, 2009 8:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:56 AM
Points: 533, Visits: 868
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
Posted Monday, March 23, 2009 8:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:53 AM
Points: 33, Visits: 117
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
Posted Monday, March 23, 2009 8:38 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:56 AM
Points: 533, Visits: 868
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
Posted Monday, March 23, 2009 9:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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


Post #681564
Posted Monday, March 23, 2009 10:17 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
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.
Post #681618
Posted Monday, March 23, 2009 10:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:45 PM
Points: 1,385, Visits: 1,245
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
Posted Monday, March 23, 2009 12:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 3, Visits: 41
But surprisingly, not triggers.
Post #681717
Posted Monday, March 23, 2009 12:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 3, Visits: 41
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 »

Add to briefcase ««1234»»»

Permissions Expand / Collapse