XML Feeds
Quite often we want to consolidate query information across all databases (or all user databases). When this question is asked in forums, the usual recommendation is to either try running undocumented sp_MSForEachDB stored procedure or do a loop and use dynamic SQL. The example of such stored procedure can be found, for example, THIRD EXAMPLE - SPROC to enumerate all objects in databases.
The idea occurred to me last night that while we do need a dynamic SQL to solve this problem, we don't really need a loop unless we need a second loop involving looping through all tables.
Here is a script demonstrating this idea - it lists indexes in all databases with their usage. This script was an answer to Help in T-SQL Query thread in MSDN T-SQL forum.
- DECLARE @SQL NVARCHAR(MAX)
- IF OBJECT_ID('tempdb..#Result','U') IS not NULL
- DROP TABLE #Result
- CREATE TABLE #Result (DBName sysname, TableName Sysname, IndexName sysname, USAGE BIGINT)
- SELECT @SQL = COALESCE(@SQL,'') + CHAR(13) + CHAR(10) + ' use ' + QUOTENAME([Name]) + ';
- insert into #Result select ' + QUOTENAME([Name],'''') + ' as DbName,
- object_name(i.object_id) as tablename, i.name as indexname,
- s.user_seeks + s.user_scans + s.user_lookups + s.user_updates as usage
- from sys.indexes i
- inner join sys.dm_db_index_usage_stats s
- on s.object_id = i.object_id
- and s.index_id = i.index_id
- and s.database_id = db_id()
- where objectproperty(i.object_id, ''IsUserTable'') = 1
- and i.index_id > 0
- order by usage;' FROM sys.databases
- --print (@SQL)
- EXECUTE (@SQL)
- SELECT * FROM #Result ORDER BY [DbName],[USAGE]
- DROP TABLE #Result
Another example of the same idea you can find in Finding Record with Last Modified date in all tables
Using this same idea you can get a count of all objects in all your databases using this PIVOT query:
- DECLARE @Qry NVARCHAR(MAX)
- SELECT @Qry = COALESCE(@Qry + CHAR(13) + CHAR(10) + ' UNION ALL ','') + '
- select ' + QUOTENAME([Name],'''') + ' as DBName, [AGGREGATE_FUNCTION], [CHECK_CONSTRAINT],[DEFAULT_CONSTRAINT],
- [FOREIGN_KEY_CONSTRAINT],
- [SQL_SCALAR_FUNCTION],
- [CLR_SCALAR_FUNCTION],
- [CLR_TABLE_VALUED_FUNCTION],
- [SQL_INLINE_TABLE_VALUED_FUNCTION],
- [INTERNAL_TABLE],[SQL_STORED_PROCEDURE],[CLR_STORED_PROCEDURE],[PLAN_GUIDE],[PRIMARY_KEY_CONSTRAINT],
- [RULE],[REPLICATION_FILTER_PROCEDURE],[SYNONYM],[SERVICE_QUEUE],[CLR_TRIGGER],[SQL_TABLE_VALUED_FUNCTION],[SQL_TRIGGER],
- [TABLE_TYPE],[USER_TABLE],[UNIQUE_CONSTRAINT],[VIEW],[EXTENDED_STORED_PROCEDURE]
- from (select [Name], type_Desc from ' + QUOTENAME([Name]) + '.sys.objects where is_ms_shipped = 0) src
- PIVOT (count([Name]) FOR type_desc in ([AGGREGATE_FUNCTION], [CHECK_CONSTRAINT],[DEFAULT_CONSTRAINT],
- [FOREIGN_KEY_CONSTRAINT],
- [SQL_SCALAR_FUNCTION],
- [CLR_SCALAR_FUNCTION],
- [CLR_TABLE_VALUED_FUNCTION],
- [SQL_INLINE_TABLE_VALUED_FUNCTION],
- [INTERNAL_TABLE],[SQL_STORED_PROCEDURE],[CLR_STORED_PROCEDURE],[PLAN_GUIDE],[PRIMARY_KEY_CONSTRAINT],
- [RULE],[REPLICATION_FILTER_PROCEDURE],[SYNONYM],[SERVICE_QUEUE],[CLR_TRIGGER],[SQL_TABLE_VALUED_FUNCTION],[SQL_TRIGGER],
- [TABLE_TYPE],[USER_TABLE],[UNIQUE_CONSTRAINT],[VIEW],[EXTENDED_STORED_PROCEDURE])) pvt' FROM sys.databases
- WHERE [name] NOT IN ('master','tempdb','model','msdb') ORDER BY [Name]
- EXECUTE(@Qry)
You can only list types you're interested in, of course.
The script below will give you a count of records in every table in a database:
- DECLARE @DynamicSQL NVARCHAR(MAX)
- SELECT @DynamicSQL = COALESCE(@DynamicSQL + CHAR(13) + ' UNION ALL ' + CHAR(13),
- '') +
- 'SELECT ' + QUOTENAME(table_schema,'''') + ' as [Schema Name], ' +
- QUOTENAME(TABLE_NAME,'''') +
- ' as [Table Name], COUNT(*) AS [Records Count] FROM ' +
- QUOTENAME(Table_schema) + '.' + QUOTENAME(TABLE_NAME)
- FROM INFORMATION_SCHEMA.TABLES
- ORDER BY TABLE_NAME
- --print (@DynamicSQL) -- we may want to use PRINT to debug the SQL
- EXEC( @DynamicSQL)
Quick row count in all tables in all databases in the server instance (you can exclude system databases from that loop, obviously)
- DECLARE @SQL NVARCHAR(MAX)
- SET @SQL = ''
- --select * from sys.databases
- SELECT @SQL = @SQL + CHAR(13) + 'USE ' + QUOTENAME([name]) + ';
- SELECT ' +QUOTENAME([name],'''') + 'as [Database Name],
- SchemaName=s.name
- ,TableName=t.name
- ,CreateDate=t.create_date
- ,ModifyDate=t.modify_date
- ,p.rows
- ,DataInKB=sum(a.used_pages)*8
- FROM sys.schemas s
- JOIN sys.tables t on s.schema_id=t.schema_id
- JOIN sys.partitions p on t.object_id=p.object_id
- JOIN sys.allocation_units a on a.container_id=p.partition_id
- GROUP BY s.name, t.name, t.create_date, t.modify_date, p.rows
- ORDER BY SchemaName, TableName' FROM sys.databases
- EXECUTE (@SQL)
Another way with less info:
- DECLARE @SQL NVARCHAR(MAX)
- SET @SQL = ''
- --select * from sys.databases
- SELECT @SQL = @SQL + CHAR(13) + 'USE ' + QUOTENAME([name]) + ';
- SELECT ' +QUOTENAME([name],'''') + 'as [Database Name], so.name AS [Table Name],
- rows AS [RowCount]
- FROM sysindexes AS si
- join sysobjects AS so on si.id = so.id
- WHERE indid IN (0,1)
- AND xtype = ''U''' FROM sys.databases
- EXECUTE (@SQL)
Here is a script showing sizes from all tables in a database.
- --exec sp_MSforeachtable 'print ''?'' exec sp_spaceused ''?'''
- IF OBJECT_ID('tempdb..#TablesSizes') IS NOT NULL
- DROP TABLE #TablesSizes
- CREATE TABLE #TablesSizes (TableName sysname, ROWS BIGINT, reserved VARCHAR(100), DATA VARCHAR(100), index_size VARCHAR(100), unused VARCHAR(100))
- DECLARE @SQL VARCHAR(MAX)
- SELECT @SQL = COALESCE(@SQL,'') + '
- insert into #TablesSizes execute sp_spaceused ' + QUOTENAME(Table_Name,'''') FROM INFORMATION_SCHEMA.TABLES
- --print (@SQL)
- EXECUTE (@SQL)
- SELECT * FROM #TablesSizes ORDER BY TableName
Here is a script showing database files sizes for all databases
- DECLARE @SQL VARCHAR(MAX)
- SELECT @SQL =COALESCE(@SQL + CHAR(13) + 'UNION ALL
- ' ,'') + 'SELECT ''' + name + ''' AS DBNAME,' +
- 'sum(size * 8 /1024.0) AS MB from ' + QUOTENAME(name) + '.dbo.sysfiles'
- FROM sys.databases
- ORDER BY name
- EXECUTE (@SQL)
The possibilities are endless.
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Naomi Nosonovsky, Senior Software Developer, has more than 15 years of enterprise experience in analysis, design, development and implementation of high-performance client/server data management solutions.
She is a personality of the year 2008 and 2009 at www.universalthread.com in .NET, SQL Server & VFP categories.
She is also an All Star contributor at forums.asp.net and MSDN T-SQL forum.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.