MS SQL 2008 - get all table names and their row co

问题:

I'm trying to identify which tables are affected by a data update though an application (developed by others). There are more than 200 tables in the DB and I'd like to avoid checking each of them for changes.

Is there any other way where I can list all table names along with their row count?

select table_name from information_schema.tables

List out all the tables in my DB buy how do I include number of rows as well?

回答1:

SELECT sc.name +'.'+ ta.name TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 ORDER BY SUM(pa.rows) DESC

See this:

• SQL SERVER – Find Row Count in Table – Find Largest Table in Database.

回答2:

to get all tables in a database:

select * from INFORMATION_SCHEMA.TABLES

to get all columns in a database:

select * from INFORMATION_SCHEMA.columns

to get all views in a db:

select * from INFORMATION_SCHEMA.TABLES where table_type = 'view'

回答3:

Posted for completeness.

If you are looking for row count of all tables in all databases (which was what I was looking for) then I found this combination of this and this to work. No idea whether it is optimal or not:

SET NOCOUNT ON
DECLARE @AllTables table (DbName sysname,SchemaName sysname, TableName sysname, RowsCount int )
DECLARE
     @SQL nvarchar(4000)
SET @SQL='SELECT ''?'' AS DbName, s.name AS SchemaName, t.name AS TableName, p.rows AS RowsCount FROM [?].sys.tables t INNER JOIN sys.schemas s ON t.schema_id=s.schema_id INNER JOIN [?].sys.partitions p ON p.OBJECT_ID = t.OBJECT_ID'

INSERT INTO @AllTables (DbName, SchemaName, TableName, RowsCount)
    EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT DbName, SchemaName, TableName, SUM(RowsCount), MIN(RowsCount), SUM(1)
FROM @AllTables
WHERE RowsCount > 0
GROUP BY DbName, SchemaName, TableName
ORDER BY DbName, SchemaName, TableName

回答4:

Try this it's simple and fast

SELECT T.name AS [TABLE NAME], I.rows AS [ROWCOUNT] 
FROM   sys.tables AS T 
   INNER JOIN sys.sysindexes AS I ON T.object_id = I.id 
   AND I.indid < 2 ORDER  BY I.rows DESC