Geeks With Blogs
Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

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

--all info from Tables

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

SELECT

*

FROM

sys.Tables

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

--Schema.name from Tables

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

SELECT

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

AS

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

BY

'['+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


Comments on this post: list schema, table, row count in a database

# re: list schema, table, row count in a database
Requesting Gravatar...
Great help on .Net and SQL server, i have shared this website with all my developer friends.
Left by Web hosting in Chile on Apr 30, 2012 2:04 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net