[WayBack] sql server – I need a slow query on AdventureWorks (SQL 2005) – Stack Overflow:
Medium slow:
SELECT * FROM Sales.SalesOrderDetail s INNER JOIN Production.Product p ON s.ProductID = p.ProductIDVery slow:
SELECT * FROM Production.TransactionHistory th INNER JOIN Production.TransactionHistoryArchive tha ON th.Quantity = tha.Quantity
It is based on the output of [WayBack] sql server – Query to list number of records in each table in a database – Stack Overflow, which originally ommited tables starting with dt (as those were be used for the “Database Diagram” in the SQL Server 7/2000 era using tables like dtProperties), but which I adopted using:
- [WayBack] Find user defined system tables
- [WayBack] sys.extended_properties (Transact-SQL) | Microsoft Docs
- [WayBack] sys.objects (Transact-SQL) | Microsoft Docs
- [WayBack] sys.tables (Transact-SQL) | Microsoft Docs
- [WayBack] sql – How do I list all non-system stored procedures? – Stack Overflow (which I verified with the above links)
- [WayBack] How to check if I can remove index from DB in SQL Server – Stack Overflow (which debunks
object_id <= 255as a criterion for SQL Server System Objects.
The query below does not support SQL Server 2000, where you would have to use things like objectproperty, but since by now even the [WayBack] documentation has been retired on the Microsoft site, you need to read [WayBack] Get list of tables but not include system tables (SQL Server 2K)? – Stack Overflow
In the end, it filters out tables like dbo.sysdiagrams that are generated by SQL Server Management Studio (SSMS), which are SSMS System Tables, but technically not SQL Server System Tables.
select
s.name as SchemaName,
t.name as TableName,
i.name as indexName,
p.rows,
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
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
inner join
sys.allocation_units a on p.partition_id = a.container_id
where
t.is_ms_shipped = 0 -- not internal to SQL Server
and
(not exists (
select ep.major_id
from sys.extended_properties ep
where
ep.major_id = t.object_id and
ep.minor_id = 0 and
ep.class = 1 and
ep.name = N'microsoft_database_tools_support'
)
) -- not internal to SQL Server Management Studio
and
i.index_id <= 1
group by
s.name, t.name, i.object_id, i.index_id, i.name, p.rows
order by
s.name, t.name
The counts for the [WayBack] GitHub version of AdventureWorks is this:
The Wiert Corner - irregular stream of stuff 