You must Sign In to post a response.
  • Category: SQL Server

    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).
  • #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

    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.

  • #766091
    Hi

    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.


    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

    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


Sign In to post your comments