CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
svbyogibear
svbyogibear

Reputation: 343

Multiple tables with the same name in SQL Server

when running the query below, I get a duplicate table returned, even though only one of it exists. Why could that be?

SELECT 
s.Name AS SchemaName,
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB, 
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
sys.tables t
INNER JOIN 
sys.schemas s ON s.schema_id = t.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
INNER JOIN 
sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
t.NAME NOT LIKE 'dt%' 
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255 
GROUP BY 
t.Name, s.Name, p.Rows
ORDER BY 
TotalSpaceKB DESC

** UPDATE The database name was displayed with a (In Recovery) tag, I just ran a script to check and it said that it is 11% completed. Could this also have caused this problem?

Upvotes: 1

Views: 4783

Answers (1)

Diego Sergnese
Diego Sergnese

Reputation: 340

As Sean pointed out, you have more than one index on the duplicated tables. To check the index name(s) you can run the slightly modified query: 

SELECT 
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
i.name,
SUM(a.total_pages) * 8 AS TotalSpaceKB, 
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
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
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows, i.name
ORDER BY 
    t.name

Upvotes: 1

Related Questions