Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: print list of queries, tables, views and sp

    I just started a new job and 1st time on sql server, how can i print list of queries, tables, views, stored procedures and functions?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What do you mean by print?
    What version of SQL Server are you running? This will have an effect on the query you need to run. The below example was written for 2000
    Code:
    SELECT name
         , id
         , type
    FROM   sysobjects
    WHERE  type IN ('V', 'U', 'SP', 'FN')
    -- v = view, u = table, sp = sproc, fn = user-defined function
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    2005 Users Note:
    BOL Says
    Important: This Microsoft SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use catalog views instead.

    Any general comments as to whether we should still be coding with sysobjects ?



    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    We are kind of caught on the edge of the sword on this issue. Because users rarely give us enough information to know what version of SQL they are using, we tend to give them the answers that work under the largest possible set of conditions.

    You are correct, using the catalog views is preferable if you are running a version of SQL Server that supports the catalog views. On a "going forward" basis, you probably ought to only use the catalog views, but on a "forum answer" I tend to stick with what will work for the largest number of people.

    -PatP

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Anybody have the catalog solution to hand?

    I don't get to play on much 2K5, but I am going to be taking my MCTS in it in a couple of months, so I should really get brushed up on it
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Play around with the view sys.objects. You should have it in no time. I think id changed to object_id, but most of the rest is the same.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT ROUTINE_TYPE, ROUTINE_SCHEMA, ROUTINE_NAME
       FROM INFORMATION_SCHEMA.ROUTINES
    
    SELECT TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME
       FROM INFORMATION_SCHEMA.TABLES
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •