Partitioning could quite possibly help you in this situation. By putting the data into a partition, the database will have a smaller table for each client to deal with. This could help performance a lot.
Here is an example of how it would help. The data in the table would otherwise be interleaved, so different clients would have rows on a data page. Reading the data from a single client would then "clutter up" the available page cache with records from other clients.
However, it will not help very much if all the clients are accessing data concurrently, so all are competing for available memory. Partitioning probably will not make this worse, but it won't necessarily help.
Also, MySQL automatically partitions the indexes on the table, which is generally a good thing.
Often partitioning is applied to a date field, so older data gets put into less used partitions. This may be a case where another field is useful. But, if all the clients need all their data at the same time, then the partitioning may not give you much of a gain.
You need a query for each database against sys.tables
.
select 'master' as DatabaseName,
T.name collate database_default as TableName
from master.sys.tables as T
union all
select 'tempdb' as DatabaseName,
T.name collate database_default as TableName
from tempdb.sys.tables as T
union all
select 'model' as DatabaseName,
T.name collate database_default as TableName
from model.sys.tables as T
union all
select 'msdb' as DatabaseName,
T.name collate database_default as TableName
from msdb.sys.tables as T
You can use sys.databases
to build and execute the query dynamically.
declare @SQL nvarchar(max)
set @SQL = (select 'union all
select '''+D.name+''' as DatabaseName,
T.name collate database_default as TableName
from '+quotename(D.name)+'.sys.tables as T
'
from sys.databases as D
for xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')
--print @SQL
exec (@SQL)
Best Answer
This query should give you what you want:
The
sys.partitions
catalog view gives a list of all partitions for tables and most indexes. Just JOIN that withsys.tables
to get the tables.All tables have at least one partition, so if you are looking specifically for partitioned tables, then you'll have to filter this query based off of
sys.partitions.partition_number <> 1
(for non-partitioned tables, thepartition_number
is always equal to 1).