list schema, table, row count in a database

-----------------------------------------------------------

--all info from Tables

-----------------------------------------------------------

SELECT

FROM

sys.Tables

-----------------------------------------------------------

--Schema.name from Tables

-----------------------------------------------------------

SELECT

'['+SCHEMA_NAME(schema_id)+'].['+name+']'

SchemaTable

FROM

sys.tables

-----------------------------------------------------------

--Schema, name from Tables

-----------------------------------------------------------

SELECT

SCHEMA_NAME(schema_id) [Schema]

name [Table]

FROM

sys.tables

-----------------------------------------------------------

--(for dropdownlist) Schema.name - rowcount, Schema.name from Tables

-----------------------------------------------------------

select

--'['+SCHEMA_NAME(t.[SCHEMA_ID])+'].['+t.NAME+']' AS [fulltable_name],

SCHEMA_NAME

(t.[SCHEMA_ID]) as [schema_name],t.NAME as [table_name] ,i.rows

from

sys.tables t

INNER

JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2)

SELECT

[TableAndCount]

= '['+SCHEMA_NAME(t.[SCHEMA_ID])+'].['+t.NAME+']' + ' - ' + CAST(i.rows as varchar(10)) + ' Rows',

[Name]

= '['+SCHEMA_NAME(t.[SCHEMA_ID])+'].['+t.NAME+']'

--[RowCount] = MAX(si.rows)

FROM

sys.tables t

INNER JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2)

ORDER

'['+SCHEMA_NAME(t.[SCHEMA_ID])+'].['+t.NAME+']' Posted on Sunday, January 8, 2012 7:40 PM SQL Server 2005 Tricks , SQL Server 2000 Tricks | Back to top