Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The Ultimate Table Searcher Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2008 11:16 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, Visits: 211
Comments posted to this topic are about the item The Ultimate Table Searcher


Post #546940
Posted Thursday, October 30, 2008 4:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 9, 2009 4:57 AM
Points: 2, Visits: 38
Great 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.

Thanks

Gaurav
http://omega.cerebrata.com
Post #594189
Posted Thursday, October 30, 2008 6:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
Jesse,

Another great article.

You could make one little add to this - return the number of Indicies for the table by adding

COUNT( index_id ) As Indicies,

to your inner join where you create partitions.

Here is my ammended code to list all tables alphabetically:
		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>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

Post #594235
Posted Tuesday, November 4, 2008 4:07 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, Visits: 211
I submitted 'The Ultimate Table Searcher Mk2' that adds PartitionCount, IndexCount, ColumnCount, TotalMaxColumnLength, HasPrimaryKey, and HasClusteredIndex.


Post #596969
Posted Wednesday, November 5, 2008 8:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
YeshuaAgapao ,

Good Deal - did you fix my mistake - count( index_id) As Indicies should be (COUNT( index_id ) - 1 ) As Indicies.

Best,
Doug
Post #597366
Posted Wednesday, November 5, 2008 8:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
Jesse,

Nice change to the program.

Doug
Post #597382
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse