Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Angry Unanswered: SQLServer2000 databases information

    Is there any table in the SQLServer2000 server in any database like master, msdb or the actual database itself that can give me information about the database owner, database properties such as: Auto update statistics ON, Torn page detection ON etc., and Recovery type like SIMPLE, FULL etc.,

    Thanks for the info in advance

  2. #2
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: SQLServer2000 databases information

    Originally posted by vkaramched
    Is there any table in the SQLServer2000 server in any database like master, msdb or the actual database itself that can give me information about the database owner, database properties such as: Auto update statistics ON, Torn page detection ON etc., and Recovery type like SIMPLE, FULL etc.,

    Thanks for the info in advance
    IF (SELECT NAME FROM SYSDATABASES WHERE NAME = 'TCLSEP')= 'TCLSEP'
    BEGIN
    EXEC sp_dboption @dbname = 'TCLSEP', @optname = 'single user', @optvalue ='TRUE'
    EXEC sp_dboption @dbname = 'TCLSEP', @optname = 'dbo use only', @optvalue ='TRUE'
    DROP DATABASE TCLSEP
    END

    This is part of a script I used to lock out a dabase befor I dropped it.
    The stored procedure sp_dboption used as exec sp_dboption @dbname = 'XXX' , @optname = 'dbo use only' will return the status of the dbo setting.

    You can probably look at the sp_dboption procedure and figure out some of what it is doing and copy the code into yur functionality.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  3. #3
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Re: SQLServer2000 databases information

    Originally posted by vkaramched
    Is there any table in the SQLServer2000 server in any database like master, msdb or the actual database itself that can give me information about the database owner, database properties such as: Auto update statistics ON, Torn page detection ON etc., and Recovery type like SIMPLE, FULL etc.,

    Thanks for the info in advance

    how about using sp_helpdb?

    (e.g. sp_helpdb blah)

  4. #4
    Join Date
    Dec 2002
    Posts
    63
    actually, if you just run sp_helpdb without specifying a database, it will give you the general info you are seeking.

  5. #5
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Re: SQLServer2000 databases information

    Originally posted by rocket39
    how about using sp_helpdb?

    (e.g. sp_helpdb blah)
    Many thanks for all the help.

Posting Permissions

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