Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 19 January 2016

SP_MSforeachDB using Filter To get all object who is having text inside objects

SQL Server: Applying Filter on sp_MSforeachDB


Working on multiple databases on a single instance, sometime you need to execute a query for each database and for that sp_MSforeachdb is the best choice.
Recently talking to my development team I came to know that a very few guys have idea about filter for sp_MSforeachDB.

For example, if I need to get database physical files information for each database on my instance, I will use following simple query
EXEC sp_MSforeachdb '
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'


BUT what if, I need to omit MSDB, TempDB and Model databases for this query. Now I have to apply filter. This can be achieved by simple IF statement.
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'

You can even use ? sign in WHERE clause.
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
       WHERE name  LIKE ''?%'' -- Only Files starting with DB name
END'

Output can be saved in tables (user, temporary) or table variables
DECLARE   @DatabasesSize TABLE
    (
      name VARCHAR(50),
      physical_name VARCHAR(500),
      state BIT,
      size INT
    )

INSERT  INTO @DatabasesSize
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'


---------------To get all object who is having text inside objects---------------
declare @SQL nvarchar(max)
 
set @SQL = ''

select @SQL = @SQL + CHAR(13) + 'USE ' + QUOTENAME([name]) + ';
SELECT ' +quotename([name],'''') + 'as [Database Name], so.name AS [Table Name],so.xtype
FROM syscomments c   
    join sysobjects AS so on c.id = so.id   
WHERE c.TEXT LIKE ''%test%''   ---------text to find inside code
    ' from sys.databases  
 
execute (@SQL)


Posted by at 05:51 0 comments

How to get information about all databases without a loop using SQL Script

Ref:

http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-get-information-about-all-databas/


Indexes in all databases with their usage

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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

Indexes in all databases with their physical stats

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

declare @SQL nvarchar(max)
set @SQL = ''
select @SQL = @SQL +
'Select ' + quotename(name,'''') + ' as [DB Name], 
object_Name(PS.Object_ID,' + convert(varchar(10),database_id) + ') as [Object],
I.Name as [Index Name], PS.Partition_Number, PS.Index_Type_Desc, 
PS.alloc_unit_type_desc,    PS.index_depth, PS.index_level,
PS.avg_fragmentation_in_percent,    PS.fragment_count,  PS.avg_fragment_size_in_pages,
PS.page_count,  PS.avg_page_space_used_in_percent,  PS.record_count,    
PS.ghost_record_count,  PS.version_ghost_record_count,
PS.min_record_size_in_bytes,    PS.max_record_size_in_bytes,    PS.avg_record_size_in_bytes,
PS.forwarded_record_count,  PS.compressed_page_count
 from ' + quotename(name) + '.sys.dm_db_index_physical_stats(' + 
convert(varchar(10),database_id) + ', NULL, NULL, NULL, NULL) PS 
INNER JOIN ' + quotename(name) + 
'.sys.Indexes I on PS.Object_ID = I.Object_ID and PS.Index_ID = I.Index_ID ' 
+ CHAR(13)
 
 from sys.databases where state_desc = 'ONLINE'
 
execute(@SQL)
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:

Count of all objects in all databases

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

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:

Record Count in every table in a database

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13

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)

Quick Record Count in All Tables in All Databases

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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:

Quick Record Count in All Tables in All Databases

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13

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.

Sizes of All Tables in a Database

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

--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
Before I show the T-SQL code I’d like to point to this very interesting blog as how to get database sizes in all SQL Server instances using PowerShell

Database Files Sizes in All Databases

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11

create  table #FileSizes (DBName sysname, [File Name] varchar(max), [Physical Name] varchar(max),
Size decimal(12,2))
declare @SQL nvarchar(max)
set @SQL = ''
select @SQL = @SQL + 'USE'  + QUOTENAME(name) + '
insert into #FileSizes
select ' + QUOTENAME(name,'''') + ', Name, Physical_Name, size/1024.0 from sys.database_files ' 
from sys.databases
 
execute (@SQL)
select * from #FileSizes order by DBName, [File Name]
You can also find the script to show all database sizes using sp_MsForEachDB here. See also this relevant thread at MSDN.
T-SQL
 
1
2
3
4
5
6
7
8

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)

Database Files Sizes in All Databases and used space

Note, that this script assumes that database files have the same name as the database itself. If this is not true, this script will not return correct result.
T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

create table #Test (DbName sysname, TotalSize decimal(20,2), Used decimal(20,2), [free space percentage] decimal(20,2))
 
declare @SQL nvarchar(max)
select @SQL = coalesce(@SQL,'') + 
'USE ' + QUOTENAME(Name) + '
insert into #Test
select DB.name, ssf.size*8 as total, 
FILEPROPERTY (AF.name, ''spaceused'')*8 as used, 
((ssf.size*8) - (FILEPROPERTY (AF.name, ''spaceused'')*8))*100/(ssf.size*8) as [free space percentage]
from sys.sysALTfiles AF 
inner join sys.sysfiles ssf on ssf.name=AF.name COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN sys.databases DB ON AF.dbid=DB.database_id 
where ssf.groupid<>1' from sys.databases
 
execute(@SQL)
 
select * from #Test order by DbName
This script will backup all databases (using compression):

Backup All Databases with Compression (SQL 2008)

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

Declare @ToExecute VarChar(8000)
 
Select @ToExecute = Coalesce(@ToExecute + 'Backup Database ' + quotename([Name]) + 
' To Disk = ''C:SQL DB BackupsAll DBs' + [Name] + '.bak'' 
WITH NOFORMAT, NOINIT,  
 SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10' + char(13),'')
 
From sys.databases
 
Where [Name] Not In ('tempdb') and databasepropertyex ([Name],'Status') = 'online'
 
Execute (@ToExecute)
 
--print @ToExecute
Get all schema names in all databases:

All Schema Names in All Databases

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13

declare @Sql nvarchar(max)
create table AllDBSchemas ([DB Name] sysname, [Schema Name] sysname)
 
select @Sql = coalesce(@Sql,'') + '
insert into AllDBSchemas
 
select ' + QUOTENAME(name,'''') + ' as [DB Name], [Name] as [Schema Name] from ' + 
QUOTENAME(Name) + '.sys.schemas order by [DB Name],[Name];' from sys.databases
order by name
 
execute(@SQL)
 
select * from AllDBSchemas order by [DB Name],[SCHEMA NAME]  

List of All Tables in All Databases

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE AllTables ([DB Name] sysname, [Schema Name] sysname, [Table Name] sysname)
 
DECLARE @SQL NVARCHAR(MAX)
 
SELECT @SQL = COALESCE(@SQL,'') + '
insert into AllTables
 
select ' + QUOTENAME(name,'''') + ' as [DB Name], [Table_Schema] as [Table Schema], [Table_Name] as [Table Name] from ' +
QUOTENAME(Name) + '.INFORMATION_SCHEMA.Tables;' FROM sys.databases
ORDER BY name
 
EXECUTE(@SQL)
 
SELECT * FROM AllTables ORDER BY [DB Name],[SCHEMA NAME], [Table Name]
Alternative way to get all tables in all databases:

List of All Tables in All Databases

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13

if object_ID('TempDB..#AllTables','U') IS NOT NULL drop table #AllTables
CREATE TABLE #AllTables ([DB Name] sysname, [Schema Name] nvarchar(128) NULL, [Table Name] sysname, create_date datetime, modify_date datetime)
 
DECLARE @SQL NVARCHAR(MAX)
 
SELECT @SQL = COALESCE(@SQL,'') + 'USE ' + quotename(name) + '
insert into #AllTables 
select ' + QUOTENAME(name,'''') + ' as [DB Name], schema_name(schema_id) as [Table Schema], [Name] as [Table Name], Create_Date, Modify_Date
 from ' +
QUOTENAME(Name) + '.sys.Tables;' FROM sys.databases
ORDER BY name
--print @SQL 
EXECUTE(@SQL)

List of all Stored Procedures in All Databases

T-SQL
 
1
2
3
4
5
6
7
8
9
10
11

create table #SPList ([DB Name] sysname, [SP Name] sysname, create_date datetime, modify_date datetime)
 
declare @SQL nvarchar(max)
set @SQL = ''
select @SQL = @SQL + ' insert into #SPList 
select ' + QUOTENAME(name, '''') + ', name, create_date, modify_date
from ' + QUOTENAME(name) + '.sys.procedures' from sys.databases
 
execute (@SQL)
 
select * from #SPList order by [DB Name], [SP Name]
Database Files Growth:
T-SQL
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

--select * from sys.sysfiles  
 
declare @SQL nvarchar(max)
select @SQL = coalesce(@SQL + '
UNION ALL ','') + 
 
'SELECT CONVERT(varchar(100),
SERVERPROPERTY(''Servername'')) AS Server, ' + 
quotename(name,'''') +'  as DatabaseName,
    CAST(name as varchar(128)) COLLATE SQL_Latin1_General_CP1_CI_AS as name,
    CAST(filename as varchar(128)) COLLATE SQL_Latin1_General_CP1_CI_AS as FileName,
    Autogrowth = ''Autogrowth: ''
        +
        CASE
            WHEN (status & 0x100000 = 0 AND CEILING((growth * 8192.0) / (1024.0 * 1024.0)) = 0.00) OR growth = 0 THEN ''None''
            WHEN status & 0x100000 = 0 THEN ''By '' + 
            CONVERT(VARCHAR,CEILING((growth * 8192.0) / (1024.0 * 1024.0))) + '' MB''
            ELSE ''By '' + CONVERT(VARCHAR,growth) + '' percent''
        END
        +
        CASE
            WHEN (status & 0x100000 = 0 AND CEILING((growth * 8192.0) / (1024.0 * 1024.0)) = 0.00) OR growth = 0 THEN ''''
            WHEN CAST([maxsize] * 8.0 / 1024 AS DEC(20,2)) <= 0.00 THEN '', unrestricted growth''
            ELSE '', restricted growth to '' + CAST(CAST([maxsize] * 8.0 / 1024 AS DEC(20)) AS VARCHAR) + '' MB''
        END
FROM '  + quotename(name) + '.sys.sysfiles  s'
from sys.databases
 
set @SQL = @SQL + ' 
ORDER BY DatabaseName'
 
print @SQL
 
execute(@SQL)
Posted by at 05:50 0 comments
Subscribe to: Posts (Atom)