Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Meta Data of System Tables

    Hi

    Is it possible, do ya know, to get the metadata of all system tables?
    For example:
    Code:
    SELECT *
    FROM sys.objects
    WHERE name = 'databases'
    I want the meta data of the sys.databases table to be returned (this is just an example, BTW - I want column information too and for loads of other tables).

    Thanks
    poots

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    misunderstood the question, I do that a lot these days.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think the best you can do is
    Code:
    sp_helptext "sys.objects"
    What do you need to do with the system metadata?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pootle flump View Post
    Is it possible, do ya know, to get the metadata of all system tables?
    If you want an answer supported by Microsoft, then I think you'll have to use SMO. There are ways to get the schema metadata for system objects using just Transact-SQL, but they are completely unsupported for end-user use by Microsoft and require some fairly "fancy dancing" to even get there.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry for the late reply guys. There might be an easier method anyway so I'll outline my problem.

    DBA left recently and it turned out he was the owner of a fair few objects. Of course his NT login is disabled. I would love to be able to query the catalogue for system objects with a owners_sid column and identify all the objects he owns. I've worked through the databases and jobs (which appear to be the most problematic objects) but would like to neaten all this up.

    We will be sorting out the ownership issue going forward but I need to clean this up first.

    Thanks

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Yeah, but owner and login are not the same? I have legacy db objects where the owner is gone, login disabled, but objects (DTS packages, sp's, etc.) still work. I had to update the owner = dbo on several objects in the past.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes, what you list will work fine - it's the "etc." I want to cover off.

    Jobs fail and some admin functions fail too (e.g. select properties for a database in SSMS owned by a disabled account and you get an error). It may be that these are the only things to worry about, and in that case I have them covered, but I would like to identify anything else in advance of it biting us on the bum.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure how to look for maintenance plans, but here is something that might get you started:
    Code:
    -- Jobs and their owners
    select name, suser_sname(owner_sid)
    from msdb..sysjobs
    
    --Schemas, objects, and their owners
    select top 10 s.name, suser_sname(p.sid), o.name
    from sys.schemas s join
    	sys.database_principals p on s.principal_id = p.principal_id left join
    	sys.objects o on s.schema_id = o.schema_id
    
    -- Databases and their owners
    select name, suser_sname(owner_sid)
    from sys.databases
    If the old DBA's account has been outright deleted, then suser_sname should return a null.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks WD.

    I've actually written some scripts to correct for all the NULL SUSER_SNAME returns for sys.databases and sysjobs. I kind of have that side cracked - I am really looking to find any other object types I need to cover off. I will check out the schemas though - thanks.

    We don't use (peuh) Maintenance Plans neeva

Posting Permissions

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