InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 19:44:40 GMT20http://www.sqlservercentral.com/Forums/Topic546940-1320-1.aspxJesse,Nice change to the program.DougWed, 05 Nov 2008 08:12:06 GMTDouglas Osborne-456728http://www.sqlservercentral.com/Forums/Topic546940-1320-1.aspxYeshuaAgapao ,Good Deal - did you fix my mistake - count( index_id) As Indicies should be (COUNT( index_id ) - 1 ) As Indicies.Best,DougWed, 05 Nov 2008 08:01:08 GMTDouglas Osborne-456728http://www.sqlservercentral.com/Forums/Topic546940-1320-1.aspxI submitted 'The Ultimate Table Searcher Mk2' that adds PartitionCount, IndexCount, ColumnCount, TotalMaxColumnLength, HasPrimaryKey, and HasClusteredIndex.Tue, 04 Nov 2008 16:07:30 GMTYeshuaAgapaohttp://www.sqlservercentral.com/Forums/Topic546940-1320-1.aspxJesse,Another great article.You could make one little add to this - return the number of Indicies for the table by addingCOUNT( index_id ) As Indicies,to your inner join where you create partitions.Here is my ammended code to list all tables alphabetically:[code] SELECT sys.objects.name AS TableName, partitions.Rows, partitions.SizeMB, partitions.Indicies, partitions.SizeMBIndexes, sys.schemas.name AS owner, sys.objects.object_id INTO dbo.DDTables FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id INNER JOIN ( SELECT object_id, SUM(row_count) AS Rows, count( index_id) As Indicies, CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(CASE WHEN index_id BETWEEN 0 AND 1 THEN in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count ELSE 0 END))/CONVERT(numeric(19,3), 128)) AS SizeMB, CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(CASE WHEN index_id&gt;1 THEN in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count ELSE 0 END))/CONVERT(numeric(19,3), 128)) AS SizeMBIndexes FROM sys.dm_db_partition_stats GROUP BY object_id ) AS partitions ON sys.objects.object_id=partitions.object_id WHERE sys.objects.type='u' ORDER BY sys.objects.name[/code]Thu, 30 Oct 2008 06:44:44 GMTDouglas Osborne-456728http://www.sqlservercentral.com/Forums/Topic546940-1320-1.aspxGreat article. It (along with other "Searcher" articles by Jesse) addresses one of the biggest problems faced by developers as to how to search metadata. One issue that I see here is that it is only for SQL Server 2005+ and will fail in SQL 2000 as it makes use of sys.schemas table which is introduced in SQL 2005. I would also recommend readers to try out search functionality in Omega.MSSQL (http://omega.cerebrata.com/r=SSCC63929), a browser based SQL server explorer developed by my company. Essentially you can search for keywords in tables (including columns, keys, triggers, indexes), views, stored procedures and all other objects just by using a browser based interface.Do check it out.ThanksGauravhttp://omega.cerebrata.comThu, 30 Oct 2008 04:56:25 GMTgaurav-628507http://www.sqlservercentral.com/Forums/Topic546940-1320-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Administration/63929/">The Ultimate Table Searcher</A>[/B]Tue, 05 Aug 2008 11:16:24 GMTYeshuaAgapao