Query returns duplicate records

Question

I have the following query to get the size of tables in all databases.

DECLARE @DBName varchar(200)
DECLARE @CMD varchar(max)
DECLARE @TV TABLE ( name_table varchar(500),ROWSCOUNT int, reserveder varchar(50), datasize varchar(50), indexsize varchar(50), unused varchar(50))
declare @tv2 table ( DBName varchar(200),name_table varchar(500),ROWSCOUNT int, reserved varchar(50), datasize float, indexsize varchar(50), unused varchar(50))
DECLARE @TV3 TABLE ( DBName varchar(200),SchemaTAble varchar(500), TableName varchar(200))
DECLARE C Cursor for select quotename(name) as dbname from sys.databases where name <> 'tempdb'  and state = 0

OPEN C

FETCH NEXT FROM C INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    DELETE FROM @TV3
    SET @CMD = 
    @DBName+'..sp_MSforeachtable ''exec sp_spaceused [?]'''
    insert into @TV
    EXEC (@CMD)

    SET @CMD = 'select '''+@DBName+''' as DatabaseName, quotename(s.name)+''.''+quotename(t.name) as SchemaTable, quotename(t.name) TableName from '+@DBName+'.sys.tables t inner join '+@DBName+'.sys.schemas s on s.schema_id = t.schema_id '
    insert into @TV3
    EXEC (@CMD)

    insert into @TV2
    select @DBName ,t.name_table,t.ROWSCOUNT,round(cast(replace(t.reserveder,' kb','')as float)/1024,2),round(cast(replace(t.datasize,' kb','') as float)/1024,2),t.indexsize,t.unused from @TV t
    inner join @TV3 s on quotename(t.name_table) = s.TableName and s.DBName = @DBName

    FETCH NEXT FROM C INTO @DBNAME
END
CLOSE C
DEALLOCate C

select   DBname+','+cast(datasize as varchar(20))+','+cast(round(cast(replace(IndexSize,' kb','')as float)/1024,2)as varchar(20))+','+cast(round(cast(replace(unused,' kb','')as float)/1024,2)as varchar(20))+','+name_table +','+cast(reserved as varchar(20))+','+'mssql'+','+cast(ROWSCOUNT as varchar(20))+','+cast(current_timestamp as varchar(20)) from @TV2 order by  datasize desc

But the query returns duplicate records in my production server.

 E.g
 [BookingDB],28027.7,4565.87,0,booking_details,32591.1,mssql,118800560,Feb
 28 2017 12:29PM
[BookingDB_Lo],28027.7,4565.87,0,booking_details,32591.1,mssql,118800560,Feb
 28 2017 12:29PM
[BookingDB_FT],28027.7,4565.87,0,booking_details,32591.1,mssql,118800560,Feb
 28 2017 12:29PM
[BookingDB_RC],28027.7,4565.87,0,booking_details,32591.1,mssql,118800560,Feb
 28 2017 12:29PM

Here the datas of same tables showing in different databases.Kindly help me to resolve this issue.

Answer 1

Try the below query that I wrote and always use to check the space used of all tables on all databases (except TempDB) has not failed me yet.

create table ##SpaceUsed
(
    [DatabaseName]  varchar(100),
    [TableName]     varchar(100),
    [RowCount]      bigint,
    [ReservedSpace] bigint,
    [DataSize]      bigint,
    [IndexSize]     bigint,
    [UnusedSpace]   bigint
)

declare @Databases as table
(
    RecordID        int IDENTITY(1,1),
    DatabaseName    varchar(100)
)

insert into @Databases(DatabaseName)
select
    Name
from
    sys.databases
where
    Name <> 'tempdb'

declare @DatabaseName           varchar(100)
declare @SQL                    varchar(MAX)
declare @CurrentDatabaseRow     int
declare @MaxDatabaseRow         int

set @CurrentDatabaseRow = (select MIN(RecordID) from @Databases)
set @MaxDatabaseRow = (select MAX(RecordID) from @Databases)

while @CurrentDatabaseRow <= @MaxDatabaseRow
begin
    set @DatabaseName = (select DatabaseName from @Databases where RecordID = @CurrentDatabaseRow)

    set @SQL = '

                        USE ['+@DatabaseName+']

                        declare @SpaceUsedTemp as table
                        (
                            [TableName]     varchar(100),
                            [RowCount]      varchar(100),
                            [ReservedSpace] varchar(100),
                            [DataSize]      varchar(100),
                            [IndexSize]     varchar(100),
                            [UnusedSpace]   varchar(100)
                        )

                        declare @Tables as table
                        (
                            RecordID    int IDENTITY(1,1),
                            SchemaName  varchar(200),
                            TableName   varchar(200)
                        )

                        -------------------------------------------------------------------
                        ---------------------Filter to user tables only--------------------
                        -------------------------------------------------------------------

                        Insert into @Tables(SchemaName, TableName)
                        select
                            s.[name],
                            t.[Name]
                        from
                            sys.tables t
                        inner join
                            sys.schemas s on s.schema_id = t.schema_id
                        --where t.type = ''U''

                        -------------------------------------------------------------------
                        -------------------------------------------------------------------

                        declare @SchemaName     varchar(200)
                        declare @TableName      varchar(200)
                        declare @FullObjectName varchar(400)
                        declare @CurrentRow     int
                        declare @MaxRow         int

                        set @CurrentRow = (select MIN(RecordID) from @Tables)
                        set @MaxRow = (select MAX(RecordID) from @Tables)

                        while @CurrentRow <= @MaxRow
                        begin
                            set @SchemaName = (select SchemaName from @Tables where RecordID = @CurrentRow)
                            set @TableName = (select TableName from @Tables where RecordID = @CurrentRow)
                            set @FullObjectName = @SchemaName + ''.'' + @TableName

                            Insert into @SpaceUsedTemp(TableName, [RowCount], [ReservedSpace], [DataSize], [IndexSize], [UnusedSpace])
                            exec sp_spaceused @FullObjectName

                            set @CurrentRow = @CurrentRow + 1
                        end

                        insert into ##SpaceUsed
                        select 
                            '''+@DatabaseName+''',
                            [TableName],
                            convert(int, REPLACE([RowCount], '' KB'', ''''))        ,
                            convert(int, REPLACE([ReservedSpace], '' KB'', ''''))   ,
                            convert(int, REPLACE([DataSize], '' KB'', ''''))        ,
                            convert(int, REPLACE([IndexSize], '' KB'', ''''))       ,
                            convert(int, REPLACE([UnusedSpace], '' KB'', '''')) 
                        from 
                            @SpaceUsedTemp


    '

    exec (@SQL)

    set @CurrentDatabaseRow = @CurrentDatabaseRow + 1
end

select * from ##SpaceUsed
order by DatabaseName, TableName

drop table ##SpaceUsed


/*
          __  __                                             
         |. ||. |    .|                                      
         || ||| |    | |                             
         |: ||: |    |'|                   ._____    
         |  ||  |   |  |     .--'|  .---"| |.   |'   
     _   |  ||  |-. |  | __  |.  | _|__  | ||   |__  
  .-'|  _|  ||  | ||   '-  | ||   |   |' | |    | |' 
  |' | |.|  ||  | ||       '-'    |   |  | |    | |  
__|  '-' '  ''  ' ""       '      `   -  |_'    ' |__

______________________________________________________

                  Joachim J Prinsloo
               http://jjprinsloo.co.za
______________________________________________________

*/

If you want to only view user tables, change the following code:

Insert into @Tables(SchemaName, TableName)
select
    s.[name],
    t.[Name]
from
    sys.tables t
inner join
    sys.schemas s on s.schema_id = t.schema_id

to this:

Insert into @Tables(SchemaName, TableName)
select
    s.[name],
    t.[Name]
from
    sys.tables t
inner join
    sys.schemas s on s.schema_id = t.schema_id
where t.type = ''U'' --WHERE CLAUSE ADDED TO SHOW ONLY USER TABLES

You can find the location of where to add the where clause in the code section marked with the following:

-------------------------------------------------------------------
---------------------Filter to user tables only--------------------
-------------------------------------------------------------------

You can just uncomment the where clause line and it will be filtered.

Hope this helps.

Answer 2

Your query is not returning duplicated rows.

I've checked it on one of my test servers:

[test],0.01,0.01,0,tt,0.02,mssql,4,Feb 28 2017  9:56AM
[test],0.01,0.02,0,btree,0.03,mssql,14,Feb 28 2017  9:56AM
[test],0,0,0,ueExpedicions,0,mssql,0,Feb 28 2017  9:56AM
[test],0,0,0,ueExpReferencies,0,mssql,0,Feb 28 2017  9:56AM
[test],0,0,0,ueContainers,0,mssql,0,Feb 28 2017  9:56AM
[test],0,0,0,t_albarans_remeses,0,mssql,0,Feb 28 2017  9:56AM
[test],0,0,0,tlbCalendar,0,mssql,0,Feb 28 2017  9:56AM
[test],0,0,0,luCalendarType,0,mssql,0,Feb 28 2017  9:56AM

If you take a look at your results:

[BookingDB],28027.7,4565.87,0,booking_details,32591.1,mssql,118800560,Feb 28 2017 12:29PM
[BookingDB_Lo],28027.7,4565.87,0,booking_details,32591.1,mssql,118800560,Feb 28 2017 12:29PM
[BookingDB_FT],28027.7,4565.87,0,booking_details,32591.1,mssql,118800560,Feb 28 2017 12:29PM
[BookingDB_RC],28027.7,4565.87,0,booking_details,32591.1,mssql,118800560,Feb 28 2017 12:29PM

I spouse you are using partitioned tables.

There is an answer on this post about how to deal with it:

https://stackoverflow.com/a/36612128/3270427