August 1, 2016 at 12:23 pm
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.
August 1, 2016 at 12:42 pm
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.
August 1, 2016 at 12:50 pm
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
August 1, 2016 at 12:55 pm
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
August 1, 2016 at 1:05 pm
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
August 3, 2016 at 4:35 am
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?
August 3, 2016 at 6:58 am
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.
August 3, 2016 at 7:24 am
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.
August 3, 2016 at 8:40 am
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
August 9, 2016 at 9:32 am
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).
August 9, 2016 at 9:38 am
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?
August 9, 2016 at 10:21 am
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
August 11, 2016 at 4:37 am
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.
August 12, 2016 at 12:08 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy