SqlQuantumLeap

SQLCLR Meta-data function for cross-database IndexName()

Oct 4th, 2015
227
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- This script is associated with the following DBA.StackExchange answer:
  2. -- http://dba.stackexchange.com/questions/44764/central-stored-procedure-to-execute-in-calling-database-context/112353#112353
  3. -- For more functions like this, please visit: http://SQLsharp.com
  4.  
  5. -- The Assembly below is compiled against .NET Framework version 2.0 so that it will work in SQL Server 2005 and
  6. -- newer (i.e. all versions that support SQLCLR).
  7.  
  8. /* -- CLEAN UP (if needed)
  9. USE [ {your_database_name} ];
  10. DROP FUNCTION [dbo].[IndexName];
  11. DROP ASSEMBLY [MetaDataFunctions];
  12. */
  13.  
  14.  
  15. -------------------------------------------------------------------------------------
  16. --                   ENABLE "CLR Integration" (ONLY IF NOT ALREADY ENABLED)
  17. -------------------------------------------------------------------------------------
  18. IF EXISTS(
  19.             SELECT *
  20.             FROM   sys.configurations sc
  21.             WHERE  sc.[name] = N'clr enabled'
  22.             AND    sc.[value_in_use] = 0
  23.         )
  24. BEGIN
  25.     PRINT 'Enabling "CLR Integration"...';
  26.     EXEC sp_configure N'clr enabled', 1;
  27.     RECONFIGURE;
  28. END;
  29. -------------------------------------------------------------------------------------
  30.  
  31.  
  32. -------------------------------------------------------------------------------------
  33. --                   CREATE OBJECTS
  34. -------------------------------------------------------------------------------------
  35. SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
  36.  
  37. USE [ {your_database_name} ];
  38. GO
  39. PRINT N'Creating [MetaDataFunctions]...';
  40. GO
  41. CREATE ASSEMBLY [MetaDataFunctions]
  42.     AUTHORIZATION [dbo]
  43.     FROM 0x\
  44. 4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409\
  45. CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300755711560000\
  46. 000000000000E00002210B010B000010000000080000000000000E2F00000020000000400000000000100020000000020000040000000000000004000000000000000080000000\
  47. 02000000000000030040850000100000100000000010000010000000000000100000000000000000000000C02E00004B00000000400000B0050000000000000000000000000000\
  48. 00000000006000000C000000882D00001C000000000000000000000000000000000000000000000000000000000000000000000000000000000000000020000008000000000000\
  49. 0000000000082000004800000000000000000000002E74657874000000140F0000002000000010000000020000000000000000000000000000200000602E72737263000000B005\
  50. 0000004000000006000000120000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200\
  51. 000000000000000000000000000000F02E000000000000480000000200050080210000080C00000100000000000000000000000000000000000000000000000000000000000000\
  52. 0000000000000000000000000000000000000000000000001B300400FD0000000100001172010000700A7215000070731100000A0B076F1200000A0C08724B0000700F00281300\
  53. 000A729F000070281400000A6F1500000A72580100701E731600000A0D090F01281700000A8C1B0000016F1800000A086F1900000A096F1A00000A26726C0100701E731600000A\
  54. 130411040F02281700000A8C1B0000016F1800000A086F1900000A11046F1A00000A26727E0100701F0C2080000000731B00000A13051105186F1C00000A086F1900000A11056F\
  55. 1A00000A26076F1D00000A086F1E00000A2611056F1F00000A7E2000000A2E0D11056F1F00000A74170000010ADE0A082C06086F2100000ADCDE0A072C06076F2100000ADC0628\
  56. 2200000A2A000000011C000002001800C8E0000A0000000002001100DBEC000A000000001E02282300000A2A42534A4201000100000000000C00000076322E302E353037323700\
  57. 00000005006C000000D0020000237E00003C0300001C04000023537472696E677300000000580700009401000023555300EC080000100000002347554944000000FC0800000C03\
  58. 000023426C6F620000000000000002000001471502000900000000FA2533001600000100000021000000020000000200000004000000230000000F000000010000000100000002\
  59. 00000000000A000100000000000600380031000A0060004B000A006A004B000A009E0083000600E000CE000600F700CE0006001401CE0006003A01CE0006005901CE0006007201\
  60. CE0006008B01CE000600A601CE000600C101CE000600DA01CE000600F301CE000600230210024300370200000600660246020600860246020A00B60283000A00E102CB020A00EF\
  61. 02CB020600120331000A00330320030A004D03CB020A005A033F000600640331000A006A0320030A008003CB020A00AA033F000A00CB0320030600ED0331000600FA0331000000\
  62. 0000010000000000010001000100100020000000050001000100502000000000960073000A00010078210000000086187D001500050000000000000000000100B00000000200BD\
  63. 0000000300C60021007D00150029007D002C0031007D002C0039007D002C0041007D002C0049007D002C0051007D002C0059007D002C0061007D002C0069007D002C0071007D00\
  64. 2C0079007D002C0081007D00310091007D00370099007D001500A1007D001500A9007D002C00A900FA02F90011000803FE00B90019030201C1003D032C00C9007D000901190008\
  65. 031001E10076031401B10097031901E900A6031E01C9007D002501E100BD032D01F900D8031500C100DD031001E100080333010101F403370109010604150011000E043C010900\
  66. 7D001500200083003C0024000B0019002E00330053022E00130050012E001B00AD012E002300CC012E002B004C022E004B00C7022E003B0087022E0043009E022E006B00DA022E\
  67. 007B00EC022E006300CD022E007300E30244000B0019004201048000000100000000000000000000000000A4020000020000000000000000000000010028000000000002000000\
  68. 000000000000000001003F000000000000000000003C4D6F64756C653E004D6574614461746146756E6374696F6E732E646C6C00496E6465786573006D73636F726C6962005379\
  69. 7374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670053716C496E74333200496E6465784E61\
  70. 6D65002E63746F72004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044617461626173654E616D65004F626A65\
  71. 6374494400496E64657849440053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C79446573637269707469\
  72. 6F6E41747472696275746500417373656D626C79496E666F726D6174696F6E616C56657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E\
  73. 41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F70\
  74. 79726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500417373\
  75. 656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044\
  76. 656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D7069\
  77. 6C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6574614461746146756E637469\
  78. 6F6E730053716C46756E6374696F6E4174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053716C436F6D6D616E64\
  79. 00437265617465436F6D6D616E64006765745F56616C756500537472696E6700436F6E6361740053797374656D2E446174612E436F6D6D6F6E004462436F6D6D616E6400736574\
  80. 5F436F6D6D616E64546578740053716C506172616D657465720053716C44625479706500496E743332004462506172616D65746572007365745F56616C75650053716C50617261\
  81. 6D65746572436F6C6C656374696F6E006765745F506172616D65746572730041646400506172616D65746572446972656374696F6E007365745F446972656374696F6E00446243\
  82. 6F6E6E656374696F6E004F70656E00457865637574654E6F6E51756572790044424E756C6C0056616C75650049446973706F7361626C6500446973706F7365006F705F496D706C\
  83. 6963697400000000133C0075006E006B006E006F0077006E003E00003543006F006E007400650078007400200043006F006E006E0065006300740069006F006E0020003D002000\
  84. 74007200750065003B0000530D000A00530045004C004500430054002000400049006E006400650078004E0061006D00650020003D002000730069002E005B006E0061006D0065\
  85. 005D000D000A00460052004F004D002000200020005B000080B75D002E005B007300790073005D002E005B0069006E00640065007800650073005D002000730069000D000A0057\
  86. 00480045005200450020002000730069002E005B006F0062006A006500630074005F00690064005D0020003D00200040004F0062006A00650063007400490044000D000A004100\
  87. 4E0044002000200020002000730069002E005B0069006E006400650078005F00690064005D0020003D002000400049006E00640065007800490044003B000D000A00001340004F\
  88. 0062006A00650063007400490044000011400049006E00640065007800490044000015400049006E006400650078004E0061006D0065000016086A7B3F1DAE4C9102E5F719BB3A\
  89. 600008B77A5C561934E0890A000311091109110D110D0320000112010001005408074D617853697A6580000000042001010E052001011145042001010880BB0100030054020F49\
  90. 7344657465726D696E6973746963015402094973507265636973650154557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D44617461416363\
  91. 6573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D\
  92. 623737613563353631393334653038391053797374656D446174614163636573730100000004200012590320000E0600030E0E0E0E062002010E116903200008042001011C0420\
  93. 00127506200112651265072003010E1169080520010111790320001C040612808105000111090E0D07060E125512591265126512655C01005753514C434C522066756E6374696F\
  94. 6E20666F722053514C20536572766572203230303520616E64206E657765722E205365652050726F70657274696573202D3E2044657461696C7320666F72206D6F726520696E66\
  95. 6F2E00001E010019687474703A2F2F53716C5175616E74756D4C6561702E636F6D00007F01007A687474703A2F2F6462612E737461636B65786368616E67652E636F6D2F717565\
  96. 7374696F6E732F34343736342F63656E7472616C2D73746F7265642D70726F6365647572652D746F2D657865637574652D696E2D63616C6C696E672D64617461626173652D636F\
  97. 6E746578742F313132333533233131323335330000060100017400003301002E53716C205175616E74756D204C656170202820687474703A2F2F53716C5175616E74756D4C6561\
  98. 702E636F6D20290000160100114D6574614461746146756E6374696F6E73000028010023436F7079726967687420C2A920323031352053716C205175616E74756D204C6561702E\
  99. 00000501000000000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100\
  100. 000000007557115600000000020000001C010000A42D0000A40F0000525344535344976BB94D0B4DA655EEF3159BD9D905000000633A5C54454D505C537461636B4F766572666C\
  101. 6F775C4D6574614461746146756E6374696F6E735C4D6574614461746146756E6374696F6E735C6F626A5C52656C656173655C4D6574614461746146756E6374696F6E732E7064\
  102. 6200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  103. 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  104. 00000000000000000000000000000000000000000000000000000000E82E00000000000000000000FE2E0000002000000000000000000000000000000000000000000000F02E00\
  105. 000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000\
  106. 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  107. 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  108. 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  109. 0000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000005405\
  110. 00000000000000000000540534000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100000000000000000000\
  111. 0000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400\
  112. 720061006E0073006C006100740069006F006E00000000000000B004B4040000010053007400720069006E006700460069006C00650049006E0066006F00000090040000010030\
  113. 00300030003000300034006200300000004C001A00010043006F006D006D0065006E0074007300000068007400740070003A002F002F00530071006C005100750061006E007400\
  114. 75006D004C006500610070002E0063006F006D00000080002F00010043006F006D00700061006E0079004E0061006D00650000000000530071006C0020005100750061006E0074\
  115. 0075006D0020004C0065006100700020002800200068007400740070003A002F002F00530071006C005100750061006E00740075006D004C006500610070002E0063006F006D00\
  116. 2000290000000000D80058000100460069006C0065004400650073006300720069007000740069006F006E0000000000530051004C0043004C0052002000660075006E00630074\
  117. 0069006F006E00200066006F0072002000530051004C00200053006500720076006500720020003200300030003500200061006E00640020006E0065007700650072002E002000\
  118. 5300650065002000500072006F00700065007200740069006500730020002D003E002000440065007400610069006C007300200066006F00720020006D006F0072006500200069\
  119. 006E0066006F002E000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000004C001600010049006E007400\
  120. 650072006E0061006C004E0061006D00650000004D006500740061004400610074006100460075006E006300740069006F006E0073002E0064006C006C0000006C00230001004C\
  121. 006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200032003000310035002000530071006C002000\
  122. 5100750061006E00740075006D0020004C006500610070002E00000000005400160001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004D\
  123. 006500740061004400610074006100460075006E006300740069006F006E0073002E0064006C006C000000440012000100500072006F0064007500630074004E0061006D006500\
  124. 000000004D006500740061004400610074006100460075006E006300740069006F006E00730000001C017B000100500072006F006400750063007400560065007200730069006F\
  125. 006E00000068007400740070003A002F002F006400620061002E0073007400610063006B00650078006300680061006E00670065002E0063006F006D002F007100750065007300\
  126. 740069006F006E0073002F00340034003700360034002F00630065006E007400720061006C002D00730074006F007200650064002D00700072006F006300650064007500720065\
  127. 002D0074006F002D0065007800650063007500740065002D0069006E002D00630061006C006C0069006E0067002D00640061007400610062006100730065002D0063006F006E00\
  128. 74006500780074002F0031003100320033003500330023003100310032003300350033000000000038000800010041007300730065006D0062006C007900200056006500720073\
  129. 0069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  130. 0000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103F0000000000000000000000000000000000000000000000\
  131. 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  132. 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  133. 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  134. 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  135. 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  136. 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  137. 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
  138.     WITH PERMISSION_SET = SAFE;
  139. GO
  140.  
  141. PRINT N'Creating [dbo].[IndexName]...';
  142. GO
  143. CREATE FUNCTION [dbo].[IndexName]
  144. (
  145.     @DatabaseName NVARCHAR(128),
  146.     @ObjectID INT,
  147.     @IndexID INT
  148. )
  149. RETURNS NVARCHAR (128)
  150. WITH    EXECUTE AS CALLER,
  151.         RETURNS NULL ON NULL INPUT
  152. AS EXTERNAL NAME [MetaDataFunctions].[Indexes].[IndexName];
  153. GO
  154. -------------------------------------------------------------------------------------
  155.  
  156.  
  157. -------------------------------------------------------------------------------------
  158. --                 TEST QUERY
  159. -------------------------------------------------------------------------------------
  160. SELECT  DB_NAME(stat.database_id) AS [DatabaseName],
  161.         OBJECT_SCHEMA_NAME(stat.[object_id], stat.database_id) AS [SchemaName],
  162.         OBJECT_NAME(stat.[object_id], stat.database_id) AS [TableName],
  163.         dbo.IndexName(DB_NAME(stat.database_id), stat.[object_id], stat.[index_id]) AS [IndexName],
  164.         stat.index_id AS [IndexID],
  165.         stat.avg_fragmentation_in_percent AS [PercentFragmentation],
  166.         stat.fragment_count AS [TotalFragments],
  167.         stat.avg_fragment_size_in_pages AS [PagesPerFragment],
  168.         stat.page_count AS [NumPages],
  169.         stat.index_type_desc AS [IndexType]
  170. FROM sys.dm_db_index_physical_stats(NULL, NULL,
  171.         NULL, NULL, 'LIMITED') stat -- LIMITED, SAMPLED, DETAILED
  172. WHERE stat.avg_fragmentation_in_percent > 10
  173. ORDER BY DatabaseName, TableName, IndexName, [PercentFragmentation] DESC;
  174. -------------------------------------------------------------------------------------
  175.  
  176. -- For more functions like this, please visit: http://SQLsharp.com
RAW Paste Data