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.
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?
This question is related to
sql-server
sql-server-2008
row
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
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
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'
Source: Stackoverflow.com