InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 17:55:35 GMT20http://www.sqlservercentral.com/Forums/Topic778916-338-1.aspxhere's one I use that was posted by Jesse Roberge ; note this one features the 2005 option where you can INCLUDE other columns in the page data so it can be accessed faster when found by the index::it returns results with every element you will need to build the actual CREATE INDEX statement, which is trivial one you've got all these pieces of the puzzle.[code]SET ANSI_NULLS ONSET QUOTED_IDENTIFIER ONSET ANSI_PADDING ONGOIF OBJECT_ID('dbo.Util_ListIndexes', 'P') IS NOT NULL DROP PROCEDURE dbo.Util_ListIndexesGO/***=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=Util_ListIndexes.sqlBy Jesse Roberge - YeshuaAgapao@Yahoo.comUpdate - Fixed existance check for dropLists details for all indexes on one or more tables / schemas, including row count and size.If you want data types and other column information and one row per index instead of one row per member column, then use Util_ListIndexes_Columns instead.Update 2009-01-14: Added IndexDepth and FillFactor output columns Added @Delimiter parameter for the column listing output (Defaults to ,) for accomodating export to delimited files. Removed duplicate output of 'is_unique'Required Input Parameters noneOptional Input Parameters @SchemaName sysname='' Filters to a single schema. Can use LIKE wildcards. All schemas if blank. Accepts LIKE Wildcards. @TableName sysname='' Filters to a single table. Can use LIKE wildcards. All tables if blank. Accepts LIKE Wildcards. @Delimiter VarChar(1)=',' Delimiter for the horizontal delimited seek and include column listings. For accomdating csv export.Usage EXECUTE Util_ListIndexes 'dbo', 'Cart'Copyright: Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software. Copyleft lets you do anything you want except plagarize, conceal the source, proprietarize modifications, or prohibit copying & re-distribution of this script/proc. This program is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details. see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=**/CREATE PROCEDURE dbo.Util_ListIndexes @SchemaName sysname='', @TableName sysname='', @Delimiter VarChar(1)=','ASSELECT sys.schemas.schema_id, sys.schemas.name AS schema_name, sys.objects.object_id, sys.objects.name AS object_name, sys.indexes.index_id, ISNULL(sys.indexes.name, '---') AS index_name, partitions.Rows, partitions.SizeMB, IndexProperty(sys.objects.object_id, sys.indexes.name, 'IndexDepth') AS IndexDepth, sys.indexes.type, sys.indexes.type_desc, sys.indexes.fill_factor, sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint, ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key, ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_includeFROM sys.objects JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id JOIN sys.indexes ON sys.objects.object_id=sys.indexes.object_id JOIN ( SELECT object_id, index_id, SUM(row_count) AS Rows, CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB FROM sys.dm_db_partition_stats GROUP BY object_id, index_id ) AS partitions ON sys.indexes.object_id=partitions.object_id AND sys.indexes.index_id=partitions.index_id CROSS APPLY ( SELECT LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key, LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include FROM ( SELECT ( SELECT sys.columns.name + @Delimiter + ' ' FROM sys.index_columns JOIN sys.columns ON sys.index_columns.column_id=sys.columns.column_id AND sys.index_columns.object_id=sys.columns.object_id WHERE sys.index_columns.is_included_column=0 AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id ORDER BY key_ordinal FOR XML PATH('') ) AS index_columns_key, ( SELECT sys.columns.name + @Delimiter + ' ' FROM sys.index_columns JOIN sys.columns ON sys.index_columns.column_id=sys.columns.column_id AND sys.index_columns.object_id=sys.columns.object_id WHERE sys.index_columns.is_included_column=1 AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id ORDER BY index_column_id FOR XML PATH('') ) AS index_columns_include ) AS Index_Columns ) AS Index_ColumnsWHERE sys.schemas.name LIKE CASE WHEN @SchemaName='' THEN sys.schemas.name ELSE @SchemaName END AND sys.objects.name LIKE CASE WHEN @TableName='' THEN sys.objects.name ELSE @TableName ENDORDER BY sys.schemas.name, sys.objects.name, sys.indexes.nameGO--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=[/code]Sun, 30 Aug 2009 17:18:17 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic778916-338-1.aspx[size="1"][i]Edited to say:[/i][/size]Reply entered by mistake, but I can't delete this entry now.Nothing to see here. Move along.... move along.Sun, 30 Aug 2009 15:42:17 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic778916-338-1.aspxwhat for?Fri, 28 Aug 2009 02:44:29 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic778916-338-1.aspxHiI want a script to get 'create statement' for all indexes existing in a database with out using sql management studio in sql2005Can any one help me on thisThanksPadmajaFri, 28 Aug 2009 00:31:13 GMTpmadhavapeddi22