Post reply

Get RowCount from all Tables in DB on RemoteServer

  • May 16, 2017 at 7:57 am

    #342855

    Hi,
    from my current SQL-Instance,
    i want to get the RowCount from all User-Tables of a  DB on a RemoteServer.
    (LinkedServer or Openquery ??)

    Thanks
    Regards
    Nicole

  • May 16, 2017 at 8:34 am

    #1942709

    You can achieve this using a linked server with the query below, just tweak it to use the linked server .

    NB:  The account used for the linked server should have VIEW DATABASE STATE Permissions on the remote server database

    SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
    FROM
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
        ON sOBJ.object_id = sPTN.object_id
    WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
    GROUP BY
      sOBJ.schema_id
      , sOBJ.name
    ORDER BY [TableName]

    SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply