Getting table names and row counts
How to get table names and their row count from 3 databases?I have 3 databases with same tables, i want my output with columns (table_name,database1_rowcount,database2_rowcount,database3_rowcount).
You must Sign In to post a response.
Category: SQL Server
I have 3 databases with same tables, i want my output with columns (table_name,database1_rowcount,database2_rowcount,database3_rowcount).
#766087
There are many ways to get the columns name and the row count of the corresponding table. You can use the following query to get the table names and row counts If you run the query against your data base you can get table names and the number of rows in that particular row
If you use the above query against your database you will get the result as you expected.
By Nathan
Direction is important than speed
SELECT o.name,
ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME
If you use the above query against your database you will get the result as you expected.
By Nathan
Direction is important than speed
#766089
I have 3 databases with same tables, i want my output with columns (table_name,database1_rowcount,database2_rowcount,database3_rowcount).
All the 3 databases are in same server.
All the 3 databases are in same server.
#766091
Hi
You can go through try this piece of Query
Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com
'Not by might nor by power, but by my Spirit,' says the LORD Almighty.
You can go through try this piece of Query
use test
select count(*) from DB1.sys.tables
select count(*) from DB2.sys.tables
select count(*) from DB3.sys.tables
OR
select count(*) from DB1.sys.tables where name='A'
select count(*) from DB2.sys.tables where name='B'
select count(*) from DB3.sys.tables where name='C'
select * from Test.sys.tables
Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com
'Not by might nor by power, but by my Spirit,' says the LORD Almighty.
#766092
You can modify the query and use based on the requirement. You can add the DB name <DB NAME>.sys.indexes in the query.
And also you can inner join or do sub query the three database query based on your table structure and get the result as you required.
By Nathan
Direction is important than speed
SELECT o.name,
ddps.row_count
FROM CP.sys.indexes AS i
INNER JOIN CP.sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN CP.sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME
And also you can inner join or do sub query the three database query based on your table structure and get the result as you required.
By Nathan
Direction is important than speed
#766129
There are multiple ways exist in SQL to get the all column names
see below snippet
hope it helps
Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]
see below snippet
Select * From [INFORMATION_SCHEMA].[COLUMNS]
select object_name(object_id) from sys.columns
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
e.g.
SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'
hope it helps
Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]
#766155
hello,
Run this query ..
SELECT
TableName = t.NAME,
TableSchema = s.Name,
RowCounts = p.rows
FROM
sys.tables t
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE
t.is_ms_shipped = 0
GROUP BY
t.NAME, s.Name, p.Rows
ORDER BY
s.Name, t.Name
Run this query ..
SELECT
TableName = t.NAME,
TableSchema = s.Name,
RowCounts = p.rows
FROM
sys.tables t
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE
t.is_ms_shipped = 0
GROUP BY
t.NAME, s.Name, p.Rows
ORDER BY
s.Name, t.Name
Return to Return to Discussion Forum