Super Quick Table Meta Data

  • Jacques-897195

    Valued Member

    Points: 71

    Comments posted to this topic are about the item Super Quick Table Meta Data

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4297

    Interesting article, it would probably benefit from at least a passing mention of the built-in stored procedure sp_help, mentioning how the custom procedure is better or more appropriate for a given situation.

    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.

  • Tommy Balle

    SSC Rookie

    Points: 31

    Hello

    I could use this metadata schema, but I could not get it to work in 2005 version.

    I got some errors when running the script:

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

    tballe

  • sandeep187

    Grasshopper

    Points: 11

    Nice piece of work but, you could also type out the table name, highlight it and press Alt + F1 in SSMS, this will return all schema information for the table including keys, indexes and constraints.

  • vivekanand-246309

    SSC Veteran

    Points: 264

    I generally highlight on the Table and Press Alt + F1 (shortcut for sp_help) which gives me all the table details..

  • Tony Webster

    SSChasing Mays

    Points: 610

    I've got something similar I wrote that I use. Possibly worth pointing out that you can bind your own stored procedures to keys using "Customise" in SSMS and QA (e.g. Options -> Environment -> Keyboard in SSIS). I guess like the author, I want something that shows the information that I think is more useful to me than alt-F1 / sp_help gives me. For example, my equivalent of alt-F1 gives the corresponding "CREATE TABLE" and "SELECT" statements for the table, which I can then just copy, paste and edit, and shows the metadata descriptions of the fields.

    I think the general point though is that you don't have to live with the metadata tools and procedures they give you - there is ample scope to extend them a bit to suit yourself better.

  • Pieter-423357

    SSCommitted

    Points: 1619

    it be nice if the code actually works. I get the same errors.

  • Carla Hepker

    Ten Centuries

    Points: 1185

    It may be that SQL 2008 allows values to be assigned to local variables in the declaration, but SQL 2005 does not.


    Regards,

    Carla Hepker
    Documentation: A Shaft of light into a coded world.

  • mtraphagen

    Old Hand

    Points: 327

    Keyboard customization is Very cool!

  • GSquared

    SSC Guru

    Points: 260824

    I like the proc. I made a modified version that works in SQL 2005 (since that's what I'm using right now). If anyone else wants to check that out, I've attached it to this post.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Jacques-897195

    Valued Member

    Points: 71

    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.

  • AlphaTangoWhiskey

    SSChampion

    Points: 10810

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

  • Adam Seniuk

    SSCrazy

    Points: 2281

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


    Over 12yrs in IT and 10yrs happily stuck with SQL.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • randal.schmidt

    Mr or Mrs. 500

    Points: 501

    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

    SSCrazy

    Points: 2281

    Please see the above attached 2005 friendly script...


    Over 12yrs in IT and 10yrs happily stuck with SQL.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply