Object Explorer Details & undocumented system stored procedures

  • Every once in a while I ask this question hoping someone has the answer. It's been a while, so I'm giving it another try.

    Back in the days of SQL 2000, someone had found the system proc that populated the equivalent of the SSMS Object Explorer Details window (the thing on the right side of the client tool, not the left side toolbar Object Explorer). This person had hacked it so that when he sorted the objects in this window, the folders (such as System Tables or System Stored Procedures) always stayed on the top of the list instead of sorting with everything else. It was a wonderful hack and I used it in my own system.

    After we upgraded to SQL 2005, I lost track of the proc in question and have been searching for it ever since (apparently I didn't save a copy off for myself). I hate sorting the Details screen and ending up with the folders halfway down my list even when I try to sort back to it's original status. Only when SSMS wigs out on me does everything reset and then I've lost several of my other customized options which I have to go back and set.

    Does anyone know the name of the undocumented system stored proc that populates and sorts the data in Object Explorer Details? I've search everywhere I can think if and don't see anything I recognize.

    If it's not a proc, is it a view? Or something else? CLR?

    Any assistance would be appreciated.

    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.

  • Running profiler against the server then navigating through the object explorer details tab, generates different dynamic sql calls.

    AFAIK, that would mean that there's no stored procedure and those are ad-hoc queries. Of course, that's just my conclusion and I could be wrong.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (8/1/2016)


    Running profiler against the server then navigating through the object explorer details tab, generates different dynamic sql calls.

    AFAIK, that would mean that there's no stored procedure and those are ad-hoc queries. Of course, that's just my conclusion and I could be wrong.

    In general, running profiler and capturing traces of stuff SSMS (ANY GUI tool actually) is a great way to both see what they are doing and often learn about SQL Server operations and management.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • This does ring a bell but haven't used Enterprise Manager for a while. IIRC the SSMS and Object Explorer Details mainly use SMO, seen some funky sql sometimes for "Intelli"sense

    😎

  • Rather than attempting to hack SSMS Object Explorer, maybe there is a SSMS add-in alternative that work in the way you want. Some people like to use RedGate SQL Search to work with a filtered group of related objects, but they would need to follow a standard naming convention.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Eric M Russell (8/1/2016)


    Rather than attempting to hack SSMS Object Explorer, maybe there is a SSMS add-in alternative that work in the way you want. Some people like to use RedGate SQL Search to work with a filtered group of related objects, but they would need to follow a standard naming convention.

    I appreciate the suggestion, Eric. But my hands are tied on third party software. We're not allowed to use unapproved stuff. Hence hacking SSMS Object Explorer Details is my best bet.

    All, I'll check into profiler. I assume I open a trace against the server I'm browsing as opposed to my local machine?

    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.

  • Brandie Tarvin (8/3/2016)


    . I assume I open a trace against the server I'm browsing as opposed to my local machine?

    Exactly, unless they're the same. I did that to reduce noise caused by other connections.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (8/3/2016)


    Brandie Tarvin (8/3/2016)


    . I assume I open a trace against the server I'm browsing as opposed to my local machine?

    Exactly, unless they're the same. I did that to reduce noise caused by other connections.

    Yeah, I was thinking of rdping over to one of the dev servers just for that reason. But I just realized I could do it against my developer edition on my new desktop and that would be even better (quieter at least).

    Thanks, Luis.

    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.

  • Brandie Tarvin (8/3/2016)

    I appreciate the suggestion, Eric. But my hands are tied on third party software. We're not allowed to use unapproved stuff. Hence hacking SSMS Object Explorer Details is my best bet.

    Time to work on approvals then. There are several add-ins I think most devs and admins should have available to them in SSMS. 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Well, this is heartbreaking...

    Poking around AdventureWorks, I get stuff like this in SQL 2008.

    exec sp_executesql N'SELECT

    udf.name AS [Name],

    SCHEMA_NAME(udf.schema_id) AS [Schema],

    ''Server[@Name='' + quotename(CAST(

    serverproperty(N''Servername'')

    AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']''

    + ''/UserDefinedFunction[@Name='' + quotename(udf.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(udf.schema_id),'''''''') + '']'' AS [Urn],

    udf.create_date AS [CreateDate],

    ISNULL(sudf.name, N'''') AS [Owner],

    (case when ''FN'' = udf.type then 1 when ''FS'' = udf.type then 1 when ''IF'' = udf.type then 3 when ''TF'' = udf.type then 2 when ''FT'' = udf.type then 2 else 0 end) AS [FunctionType]

    FROM

    sys.all_objects AS udf

    LEFT OUTER JOIN sys.database_principals AS sudf ON sudf.principal_id = ISNULL(udf.principal_id, (OBJECTPROPERTY(udf.object_id, ''OwnerId'')))

    WHERE

    (udf.type in (''TF'', ''FN'', ''IF'', ''FS'', ''FT''))and(((case when ''FN'' = udf.type then 1 when ''FS'' = udf.type then 1

    when ''IF'' = udf.type then 3 when ''TF'' = udf.type then 2 when ''FT'' = udf.type then 2 else 0 end)=@_msparam_0

    or (case when ''FN'' = udf.type then 1 when ''FS'' = udf.type then 1 when ''IF'' = udf.type then 3 when ''TF'' = udf.type then 2

    when ''FT'' = udf.type then 2 else 0 end)=@_msparam_1) and CAST(

    case

    when udf.is_ms_shipped = 1 then 1

    when (

    select

    major_id

    from

    sys.extended_properties

    where

    major_id = udf.object_id and

    minor_id = 0 and

    class = 1 and

    name = N''microsoft_database_tools_support'')

    is not null then 1

    else 0

    end

    AS bit)=@_msparam_2)

    ORDER BY

    [Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'3',@_msparam_1=N'2',@_msparam_2=N'0'

    -----------------------------------

    exec sp_executesql N'

    DECLARE @is_policy_automation_enabled bit

    SET @is_policy_automation_enabled = (SELECT CONVERT(bit, current_value)

    FROM msdb.dbo.syspolicy_configuration

    WHERE name = ''Enabled'')

    SELECT

    CAST(

    serverproperty(N''Servername'')

    AS sysname) AS [Server_Name],

    db_name() AS [Database_Name],

    SCHEMA_NAME(sp.schema_id) AS [Schema],

    sp.name AS [Name],

    ''Server[@Name='' + quotename(CAST(

    serverproperty(N''Servername'')

    AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/StoredProcedure[@Name='' + quotename(sp.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(sp.schema_id),'''''''') + '']'' AS [Urn],

    case when amsp.object_id is null then N'''' else asmblsp.name end AS [AssemblyName],

    case when amsp.object_id is null then N'''' else amsp.assembly_class end AS [ClassName],

    sp.create_date AS [CreateDate],

    sp.modify_date AS [DateLastModified],

    CAST(CASE sp.type WHEN N''RF'' THEN 1 ELSE 0 END AS bit) AS [ForReplication],

    CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],

    CAST(

    case

    when sp.is_ms_shipped = 1 then 1

    when (

    select

    major_id

    from

    sys.extended_properties

    where

    major_id = sp.object_id and

    minor_id = 0 and

    class = 1 and

    name = N''microsoft_database_tools_support'')

    is not null then 1

    else 0

    end

    AS bit) AS [IsSystemObject],

    CAST(ISNULL(smsp.is_recompiled, ssmsp.is_recompiled) AS bit) AS [Recompile],

    case when 1=@is_policy_automation_enabled and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like ''Server'' + ''/Database\[@ID='' + convert(nvarchar(20),dtb.database_id) + ''\]''+ ''/StoredProcedure\[@ID='' + convert(nvarchar(20),sp.object_id) + ''\]%'' ESCAPE ''\'') then 1 else 0 end AS [PolicyHealthState]

    FROM

    master.sys.databases AS dtb,

    sys.all_objects AS sp

    LEFT OUTER JOIN sys.assembly_modules AS amsp ON amsp.object_id = sp.object_id

    LEFT OUTER JOIN sys.assemblies AS asmblsp ON asmblsp.assembly_id = amsp.assembly_id

    LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id

    LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id

    WHERE

    (sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(sp.name=@_msparam_3 and SCHEMA_NAME(sp.schema_id)=@_msparam_4)and((dtb.name=db_name()))

    ORDER BY

    [Database_Name] ASC,[Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'uspUpdateEmployeePersonalInfo',@_msparam_4=N'HumanResources'

    I'm having issues with my local copy of SQL 2012, so I tried 2008 figuring the results would be similar or the same. It appears, unless I'm misreading this, these are ad hoc queries off of catalog views.

    I have to be missing something. I've attached the trace file (with an added '.txt' to the end of it so it would upload). Anyone have any thoughts on this?

    Perhaps Profiler can't see the actual proc names? (Also uploaded a copy of the profiler template I used. Advice on changing it would be appreciated).

    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.

  • TheSQLGuru (8/3/2016)


    Brandie Tarvin (8/3/2016)

    I appreciate the suggestion, Eric. But my hands are tied on third party software. We're not allowed to use unapproved stuff. Hence hacking SSMS Object Explorer Details is my best bet.

    Time to work on approvals then. There are several add-ins I think most devs and admins should have available to them in SSMS. 🙂

    Do you have a list? I'd love to see your recommendations.

    Also, do any of them replicate Object Explorer Details functionality?

    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.

  • Brandie Tarvin (8/9/2016)


    TheSQLGuru (8/3/2016)


    Brandie Tarvin (8/3/2016)

    I appreciate the suggestion, Eric. But my hands are tied on third party software. We're not allowed to use unapproved stuff. Hence hacking SSMS Object Explorer Details is my best bet.

    Time to work on approvals then. There are several add-ins I think most devs and admins should have available to them in SSMS. 🙂

    Do you have a list? I'd love to see your recommendations.

    Also, do any of them replicate Object Explorer Details functionality?

    Try http://www.sqltreeo.com/ for some Object Explorer enhancements. I've not used it but it looks like it could meet your needs.

    SQLJudo did a 30-day series on creating your own SSMS Add-in. Why don't you just build what you need (and then make a product out of it and become a bajillionaire)!! 😀

    For actual SSMS add-ins I think SSMS Tools Pack is the one to have. If you are a tuner then Plan Explorer from SQL Sentry is mandatory and has an SSMS Add-in.

    Most major vendors and some smaller ones have both free and for-fee SSMS add-ins covering a variety of dev and admin needs.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (8/9/2016)


    SQLJudo did a 30-day series on creating your own SSMS Add-in. Why don't you just build what you need (and then make a product out of it and become a bajillionaire)!! 😀

    HA! I would love to build out what I need. Thank you for the reference. I'll go look it up.

    Try http://www.sqltreeo.com/ for some Object Explorer enhancements. I've not used it but it looks like it could meet your needs.

    ....

    For actual SSMS add-ins I think SSMS Tools Pack is the one to have. If you are a tuner then Plan Explorer from SQL Sentry is mandatory and has an SSMS Add-in.

    Most major vendors and some smaller ones have both free and for-fee SSMS add-ins covering a variety of dev and admin needs.

    Thank you for this. I'll take a look at everything.

    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.

  • Brandie Tarvin (8/11/2016)


    TheSQLGuru (8/9/2016)


    SQLJudo did a 30-day series on creating your own SSMS Add-in. Why don't you just build what you need (and then make a product out of it and become a bajillionaire)!! 😀

    HA! I would love to build out what I need. Thank you for the reference. I'll go look it up.

    Try http://www.sqltreeo.com/ for some Object Explorer enhancements. I've not used it but it looks like it could meet your needs.

    ....

    For actual SSMS add-ins I think SSMS Tools Pack is the one to have. If you are a tuner then Plan Explorer from SQL Sentry is mandatory and has an SSMS Add-in.

    Most major vendors and some smaller ones have both free and for-fee SSMS add-ins covering a variety of dev and admin needs.

    Thank you for this. I'll take a look at everything.

    Let us know what you find out about them. I'm also very interested in seeing what else you come up with for what SSMS fires to build the tree. Some of the MS code I've seen makes me shudder and I'm hoping this will be different.

Viewing 14 posts - 1 through 14 (of 14 total)

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