Post reply

sys.objects$ (with a Dollar sign)

  • July 27, 2011 at 11:22 am

    #390067

    I was wondering why there wasn't a sys.functions view similar to sys.procedures,sys.tables and sys.objects.

    if you run exec sp_helptext sys.procedures in SQL 2008, you get this definition:

    CREATE VIEW sys.procedures AS

    SELECT name, object_id, principal_id, schema_id, parent_object_id,

    type, type_desc, create_date, modify_date,

    is_ms_shipped, is_published, is_schema_published,

    is_auto_executed, is_execution_replicated,

    is_repl_serializable_only, skips_repl_constraints

    FROM sys.objects$

    WHERE type IN ('P ', 'X ', 'PC', 'RF')

    I cannot copy paste the definition inside the proc and run it seperately, i get an error

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sys.objects$'.

    and the regular view, sys.objects, does not have the same column names....similar, but not exactly the same.

    Msg 207, Level 16, State 1, Line 14

    Invalid column name 'is_auto_executed'.

    Msg 207, Level 16, State 1, Line 15

    Invalid column name 'is_execution_replicated'.

    Msg 207, Level 16, State 1, Line 16

    Invalid column name 'is_repl_serializable_only'.

    Msg 207, Level 16, State 1, Line 17

    Invalid column name 'skips_repl_constraints'.

    so is that the super secret resource database? how can i select from it, if i reeeallly wanted to?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 27, 2011 at 11:29 am

    #1359538

    There is an internal stored procedure taht display all stored procedures in an instance with a detailed information.

    insert INTO #temp1

    EXEC sp_stored_procedures

    hope fully helps

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • July 27, 2011 at 11:29 am

    #1359539

    Shut down SQL, copy the data and log files of the system resource, start SQL, attach them as a user database and play. On a dev/test server.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 27, 2011 at 11:32 am

    #1359542

    Lowell (7/27/2011)

    so is that the super secret resource database? how can i select from it, if i reeeallly wanted to?

    Bad Lowell. We're not supposed to be selecting from Microsoft's secret stash. Shame on you... (shakes finger while trying to figure it out herself).

    Actually, if MS is following the Windows convention, the $ is used to indicate hidden shares on the OS. Or, in this case, hidden tables, that are accessible by something. I wonder if they have super-secret permissions levels that are higher than SysAdmin which gives objects access to their hidden tables during the course of normal processing.

    Hmmm. Square brackets doesn't help the issue. But "sysobjects" still works in 2008.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • July 27, 2011 at 11:43 am

    #1359555

    GilaMonster (7/27/2011)

    Shut down SQL, copy the data and log files of the system resource, start SQL, attach them as a user database and play. On a dev/test server.

    This is quite an informative method. I have done this on several occasions and have learned a lot from the resource database.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 27, 2011 at 11:47 am

    #1359562

    Brandie Tarvin (7/27/2011)

    I wonder if they have super-secret permissions levels that are higher than SysAdmin which gives objects access to their hidden tables during the course of normal processing.

    Typically they just can't be bound, so when the batch is submitted to SQL, the parser throws it out as an invalid object, because it's coded to do that. Same reason that SELECT * FROM sys.sysrscols fails, even though the table is visible in sys.objects

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 27, 2011 at 11:55 am

    #1359570

    I don't see either view or table called sys.objects$ (just did the Resource user db on my local dev copy).

    Lowell, make sure to rename the db before you try to attach it, or it will yell at you for messing with the Resource db.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • July 27, 2011 at 12:08 pm

    #1359582

    Thanks everyone;

    I've attached it successfully as a user database and will now make a huge mess of things.

    Thanks!

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 27, 2011 at 12:10 pm

    #1359583

    Lowell (7/27/2011)

    Thanks everyone;

    I've attached it successfully as a user database and will now make a huge mess of things.

    Thanks!

    Oh crap, my first feeling is that the baby had been burnt.

    I'm all ok now :sick:

  • July 27, 2011 at 12:17 pm

    #1359585

    Lowell (7/27/2011)

    Thanks everyone;

    I've attached it successfully as a user database and will now make a huge mess of things.

    Thanks!

    I am sure it resembles either Brandie, Ninja ,Lowell or Gila monster. Now be honest:-D

  • July 27, 2011 at 12:24 pm

    #1359591

    chandan_jha18 (7/27/2011)

    Lowell (7/27/2011)

    Thanks everyone;

    I've attached it successfully as a user database and will now make a huge mess of things.

    Thanks!

    I am sure it resembles either Brandie, Ninja ,Lowell or Gila monster. Now be honest:-D

    Well ya, I'm the youngest here, seen my avatar lately :-D.

    And ya, I'm sure I've done that in the last decade or so :-P.

  • March 28, 2013 at 2:42 pm

    #1601870

    Sorry to bump this from the grave, but in response to those wondering whether there was some super secret "super-administrative" access level, I just found out that you do in fact have direct select access to sys.objects$ if you connect through DAC (see http://msdn.microsoft.com/en-us/library/ms189595.aspx). http://msdn.microsoft.com/en-us/library/ms179503(v=sql.105).aspx explains why DAC is required. You can select from sys.sysrscols as well, incidentally.

Viewing 12 posts - 1 through 11 (of 11 total)

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