Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- This script is associated with the following DBA.StackExchange answer:
- -- http://dba.stackexchange.com/questions/44764/central-stored-procedure-to-execute-in-calling-database-context/112353#112353
- -- For more functions like this, please visit: http://SQLsharp.com
- -- The Assembly below is compiled against .NET Framework version 2.0 so that it will work in SQL Server 2005 and
- -- newer (i.e. all versions that support SQLCLR).
- /* -- CLEAN UP (if needed)
- USE [ {your_database_name} ];
- DROP FUNCTION [dbo].[IndexName];
- DROP ASSEMBLY [MetaDataFunctions];
- */
- -------------------------------------------------------------------------------------
- -- ENABLE "CLR Integration" (ONLY IF NOT ALREADY ENABLED)
- -------------------------------------------------------------------------------------
- IF EXISTS(
- SELECT *
- FROM sys.configurations sc
- WHERE sc.[name] = N'clr enabled'
- AND sc.[value_in_use] = 0
- )
- BEGIN
- PRINT 'Enabling "CLR Integration"...';
- EXEC sp_configure N'clr enabled', 1;
- RECONFIGURE;
- END;
- -------------------------------------------------------------------------------------
- -------------------------------------------------------------------------------------
- -- CREATE OBJECTS
- -------------------------------------------------------------------------------------
- SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
- USE [ {your_database_name} ];
- GO
- PRINT N'Creating [MetaDataFunctions]...';
- GO
- CREATE ASSEMBLY [MetaDataFunctions]
- AUTHORIZATION [dbo]
- FROM 0x\
- 4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409\
- CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300755711560000\
- 000000000000E00002210B010B000010000000080000000000000E2F00000020000000400000000000100020000000020000040000000000000004000000000000000080000000\
- 02000000000000030040850000100000100000000010000010000000000000100000000000000000000000C02E00004B00000000400000B0050000000000000000000000000000\
- 00000000006000000C000000882D00001C000000000000000000000000000000000000000000000000000000000000000000000000000000000000000020000008000000000000\
- 0000000000082000004800000000000000000000002E74657874000000140F0000002000000010000000020000000000000000000000000000200000602E72737263000000B005\
- 0000004000000006000000120000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200\
- 000000000000000000000000000000F02E000000000000480000000200050080210000080C00000100000000000000000000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000001B300400FD0000000100001172010000700A7215000070731100000A0B076F1200000A0C08724B0000700F00281300\
- 000A729F000070281400000A6F1500000A72580100701E731600000A0D090F01281700000A8C1B0000016F1800000A086F1900000A096F1A00000A26726C0100701E731600000A\
- 130411040F02281700000A8C1B0000016F1800000A086F1900000A11046F1A00000A26727E0100701F0C2080000000731B00000A13051105186F1C00000A086F1900000A11056F\
- 1A00000A26076F1D00000A086F1E00000A2611056F1F00000A7E2000000A2E0D11056F1F00000A74170000010ADE0A082C06086F2100000ADCDE0A072C06076F2100000ADC0628\
- 2200000A2A000000011C000002001800C8E0000A0000000002001100DBEC000A000000001E02282300000A2A42534A4201000100000000000C00000076322E302E353037323700\
- 00000005006C000000D0020000237E00003C0300001C04000023537472696E677300000000580700009401000023555300EC080000100000002347554944000000FC0800000C03\
- 000023426C6F620000000000000002000001471502000900000000FA2533001600000100000021000000020000000200000004000000230000000F000000010000000100000002\
- 00000000000A000100000000000600380031000A0060004B000A006A004B000A009E0083000600E000CE000600F700CE0006001401CE0006003A01CE0006005901CE0006007201\
- CE0006008B01CE000600A601CE000600C101CE000600DA01CE000600F301CE000600230210024300370200000600660246020600860246020A00B60283000A00E102CB020A00EF\
- 02CB020600120331000A00330320030A004D03CB020A005A033F000600640331000A006A0320030A008003CB020A00AA033F000A00CB0320030600ED0331000600FA0331000000\
- 0000010000000000010001000100100020000000050001000100502000000000960073000A00010078210000000086187D001500050000000000000000000100B00000000200BD\
- 0000000300C60021007D00150029007D002C0031007D002C0039007D002C0041007D002C0049007D002C0051007D002C0059007D002C0061007D002C0069007D002C0071007D00\
- 2C0079007D002C0081007D00310091007D00370099007D001500A1007D001500A9007D002C00A900FA02F90011000803FE00B90019030201C1003D032C00C9007D000901190008\
- 031001E10076031401B10097031901E900A6031E01C9007D002501E100BD032D01F900D8031500C100DD031001E100080333010101F403370109010604150011000E043C010900\
- 7D001500200083003C0024000B0019002E00330053022E00130050012E001B00AD012E002300CC012E002B004C022E004B00C7022E003B0087022E0043009E022E006B00DA022E\
- 007B00EC022E006300CD022E007300E30244000B0019004201048000000100000000000000000000000000A4020000020000000000000000000000010028000000000002000000\
- 000000000000000001003F000000000000000000003C4D6F64756C653E004D6574614461746146756E6374696F6E732E646C6C00496E6465786573006D73636F726C6962005379\
- 7374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670053716C496E74333200496E6465784E61\
- 6D65002E63746F72004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044617461626173654E616D65004F626A65\
- 6374494400496E64657849440053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C79446573637269707469\
- 6F6E41747472696275746500417373656D626C79496E666F726D6174696F6E616C56657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E\
- 41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F70\
- 79726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500417373\
- 656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044\
- 656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D7069\
- 6C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6574614461746146756E637469\
- 6F6E730053716C46756E6374696F6E4174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053716C436F6D6D616E64\
- 00437265617465436F6D6D616E64006765745F56616C756500537472696E6700436F6E6361740053797374656D2E446174612E436F6D6D6F6E004462436F6D6D616E6400736574\
- 5F436F6D6D616E64546578740053716C506172616D657465720053716C44625479706500496E743332004462506172616D65746572007365745F56616C75650053716C50617261\
- 6D65746572436F6C6C656374696F6E006765745F506172616D65746572730041646400506172616D65746572446972656374696F6E007365745F446972656374696F6E00446243\
- 6F6E6E656374696F6E004F70656E00457865637574654E6F6E51756572790044424E756C6C0056616C75650049446973706F7361626C6500446973706F7365006F705F496D706C\
- 6963697400000000133C0075006E006B006E006F0077006E003E00003543006F006E007400650078007400200043006F006E006E0065006300740069006F006E0020003D002000\
- 74007200750065003B0000530D000A00530045004C004500430054002000400049006E006400650078004E0061006D00650020003D002000730069002E005B006E0061006D0065\
- 005D000D000A00460052004F004D002000200020005B000080B75D002E005B007300790073005D002E005B0069006E00640065007800650073005D002000730069000D000A0057\
- 00480045005200450020002000730069002E005B006F0062006A006500630074005F00690064005D0020003D00200040004F0062006A00650063007400490044000D000A004100\
- 4E0044002000200020002000730069002E005B0069006E006400650078005F00690064005D0020003D002000400049006E00640065007800490044003B000D000A00001340004F\
- 0062006A00650063007400490044000011400049006E00640065007800490044000015400049006E006400650078004E0061006D0065000016086A7B3F1DAE4C9102E5F719BB3A\
- 600008B77A5C561934E0890A000311091109110D110D0320000112010001005408074D617853697A6580000000042001010E052001011145042001010880BB0100030054020F49\
- 7344657465726D696E6973746963015402094973507265636973650154557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D44617461416363\
- 6573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D\
- 623737613563353631393334653038391053797374656D446174614163636573730100000004200012590320000E0600030E0E0E0E062002010E116903200008042001011C0420\
- 00127506200112651265072003010E1169080520010111790320001C040612808105000111090E0D07060E125512591265126512655C01005753514C434C522066756E6374696F\
- 6E20666F722053514C20536572766572203230303520616E64206E657765722E205365652050726F70657274696573202D3E2044657461696C7320666F72206D6F726520696E66\
- 6F2E00001E010019687474703A2F2F53716C5175616E74756D4C6561702E636F6D00007F01007A687474703A2F2F6462612E737461636B65786368616E67652E636F6D2F717565\
- 7374696F6E732F34343736342F63656E7472616C2D73746F7265642D70726F6365647572652D746F2D657865637574652D696E2D63616C6C696E672D64617461626173652D636F\
- 6E746578742F313132333533233131323335330000060100017400003301002E53716C205175616E74756D204C656170202820687474703A2F2F53716C5175616E74756D4C6561\
- 702E636F6D20290000160100114D6574614461746146756E6374696F6E73000028010023436F7079726967687420C2A920323031352053716C205175616E74756D204C6561702E\
- 00000501000000000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100\
- 000000007557115600000000020000001C010000A42D0000A40F0000525344535344976BB94D0B4DA655EEF3159BD9D905000000633A5C54454D505C537461636B4F766572666C\
- 6F775C4D6574614461746146756E6374696F6E735C4D6574614461746146756E6374696F6E735C6F626A5C52656C656173655C4D6574614461746146756E6374696F6E732E7064\
- 6200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
- 00000000000000000000000000000000000000000000000000000000E82E00000000000000000000FE2E0000002000000000000000000000000000000000000000000000F02E00\
- 000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
- 0000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000005405\
- 00000000000000000000540534000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100000000000000000000\
- 0000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400\
- 720061006E0073006C006100740069006F006E00000000000000B004B4040000010053007400720069006E006700460069006C00650049006E0066006F00000090040000010030\
- 00300030003000300034006200300000004C001A00010043006F006D006D0065006E0074007300000068007400740070003A002F002F00530071006C005100750061006E007400\
- 75006D004C006500610070002E0063006F006D00000080002F00010043006F006D00700061006E0079004E0061006D00650000000000530071006C0020005100750061006E0074\
- 0075006D0020004C0065006100700020002800200068007400740070003A002F002F00530071006C005100750061006E00740075006D004C006500610070002E0063006F006D00\
- 2000290000000000D80058000100460069006C0065004400650073006300720069007000740069006F006E0000000000530051004C0043004C0052002000660075006E00630074\
- 0069006F006E00200066006F0072002000530051004C00200053006500720076006500720020003200300030003500200061006E00640020006E0065007700650072002E002000\
- 5300650065002000500072006F00700065007200740069006500730020002D003E002000440065007400610069006C007300200066006F00720020006D006F0072006500200069\
- 006E0066006F002E000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000004C001600010049006E007400\
- 650072006E0061006C004E0061006D00650000004D006500740061004400610074006100460075006E006300740069006F006E0073002E0064006C006C0000006C00230001004C\
- 006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200032003000310035002000530071006C002000\
- 5100750061006E00740075006D0020004C006500610070002E00000000005400160001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004D\
- 006500740061004400610074006100460075006E006300740069006F006E0073002E0064006C006C000000440012000100500072006F0064007500630074004E0061006D006500\
- 000000004D006500740061004400610074006100460075006E006300740069006F006E00730000001C017B000100500072006F006400750063007400560065007200730069006F\
- 006E00000068007400740070003A002F002F006400620061002E0073007400610063006B00650078006300680061006E00670065002E0063006F006D002F007100750065007300\
- 740069006F006E0073002F00340034003700360034002F00630065006E007400720061006C002D00730074006F007200650064002D00700072006F006300650064007500720065\
- 002D0074006F002D0065007800650063007500740065002D0069006E002D00630061006C006C0069006E0067002D00640061007400610062006100730065002D0063006F006E00\
- 74006500780074002F0031003100320033003500330023003100310032003300350033000000000038000800010041007300730065006D0062006C007900200056006500720073\
- 0069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103F0000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
- 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
- WITH PERMISSION_SET = SAFE;
- GO
- PRINT N'Creating [dbo].[IndexName]...';
- GO
- CREATE FUNCTION [dbo].[IndexName]
- (
- @DatabaseName NVARCHAR(128),
- @ObjectID INT,
- @IndexID INT
- )
- RETURNS NVARCHAR (128)
- WITH EXECUTE AS CALLER,
- RETURNS NULL ON NULL INPUT
- AS EXTERNAL NAME [MetaDataFunctions].[Indexes].[IndexName];
- GO
- -------------------------------------------------------------------------------------
- -------------------------------------------------------------------------------------
- -- TEST QUERY
- -------------------------------------------------------------------------------------
- SELECT DB_NAME(stat.database_id) AS [DatabaseName],
- OBJECT_SCHEMA_NAME(stat.[object_id], stat.database_id) AS [SchemaName],
- OBJECT_NAME(stat.[object_id], stat.database_id) AS [TableName],
- dbo.IndexName(DB_NAME(stat.database_id), stat.[object_id], stat.[index_id]) AS [IndexName],
- stat.index_id AS [IndexID],
- stat.avg_fragmentation_in_percent AS [PercentFragmentation],
- stat.fragment_count AS [TotalFragments],
- stat.avg_fragment_size_in_pages AS [PagesPerFragment],
- stat.page_count AS [NumPages],
- stat.index_type_desc AS [IndexType]
- FROM sys.dm_db_index_physical_stats(NULL, NULL,
- NULL, NULL, 'LIMITED') stat -- LIMITED, SAMPLED, DETAILED
- WHERE stat.avg_fragmentation_in_percent > 10
- ORDER BY DatabaseName, TableName, IndexName, [PercentFragmentation] DESC;
- -------------------------------------------------------------------------------------
- -- For more functions like this, please visit: http://SQLsharp.com
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement