How to get information about all databases

‎/*
**********For All Database Administrator (DBA)******************
*****How to get information about all databases without a loop********
1-ndexes in all databases with their usage (1 Script)
2-Indexes in all databases with their physical stats (1 Script)
3-Count of all objects in all databases (1 Script)
4-Quick Record Count in All Tables in All Databases (2 Script)
5-All Schema Names in All Databases (1 Script)
6-List of All Tables in All Databases (2 Script)
7-LIST OF ALL procedures IN ALL DATABASES (1 Script)
8-LIST OF ALL VIEWS IN ALL DATABASES (1 Script)
9- Size Tales in Database Return
(Count Of Rows,Reserved Size,Data Size,Index Size,UnUsed Size) (2 Script)
10-Database File Size (MDF.LDF.NDF)in ALL Databases (1 Script)
11-Database SIZE (2 Script)
*/
——————————————————————————————
—How do I know which version of SQL Server I’m running?
SELECT ‘SQL Server ‘
+ CAST(SERVERPROPERTY(‘productversion’) AS VARCHAR) + ‘ – ‘
+ CAST(SERVERPROPERTY(‘productlevel’) AS VARCHAR) + ‘ (‘
+ CAST(SERVERPROPERTY(‘edition’) AS VARCHAR) + ‘)’
—————————————————————————————————–
–Some Info to all databases
SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, ‘status’)) AS [Status],
state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE compatibility_level
WHEN 60 THEN ’60 (SQL Server 6.0)’
WHEN 65 THEN ’65 (SQL Server 6.5)’
WHEN 70 THEN ’70 (SQL Server 7.0)’
WHEN 80 THEN ’80 (SQL Server 2000)’
WHEN 90 THEN ’90 (SQL Server 2005)’
WHEN 100 THEN ‘100 (SQL Server 2008)’
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
— last backup
ISNULL((SELECT TOP 1
CASE TYPE WHEN ‘D’ THEN ‘Full’ WHEN ‘I’ THEN ‘Differential’ WHEN ‘L’ THEN ‘Transaction log’ END + ‘ – ‘ +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ‘ days ago’, ‘NEVER’)) + ‘ – ‘ +
CONVERT(VARCHAR(20), backup_start_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_start_date, 108) + ‘ – ‘ +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_finish_date, 108) +
‘ (‘ + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ‘ ‘
+ ‘seconds)’
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),’-‘) AS [Last backup],
CASE WHEN is_fulltext_enabled = 1 THEN ‘Fulltext enabled’ ELSE ” END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN ‘autoclose’ ELSE ” END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN ‘read only’ ELSE ” END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN ‘autoshrink’ ELSE ” END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN ‘auto create statistics’ ELSE ” END AS [auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN ‘auto update statistics’ ELSE ” END AS [auto update statistics],
CASE WHEN is_in_standby = 1 THEN ‘standby’ ELSE ” END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN ‘cleanly shutdown’ ELSE ” END AS [cleanly shutdown]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME
———————————————————————————————
—–1. To list down all databases in the server—–
SELECT NAME AS [DataBase Name] FROM SYS.DATABASES ORDER BY NAME
———————————————————————————————-
–2. To list down all databases in the server along with the state(online / offline / restoring / — recovering / recovering_pending / suspect / emergency)—–
SELECT NAME AS [Database Name],STATE_DESC AS [Database Status] FROM SYS.DATABASES ORDER BY NAME
———————————————————————————
—–3. To check the state of a particular database—–
SELECT NAME AS [Database Name],STATE_DESC AS [Database State]
FROM SYS.DATABASES WHERE NAME = ‘RGEN2011’
———————————————————————————————————-

–Indexes in all databases with their usage
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
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)
———————————————————————————-
–Count of all objects in all databases
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],

EXECUTE(@Qry)
————————————————————————-
–Quick Record Count in All Tables in All Databases
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)
————————————
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)
————————————————————————————–
—All Schema Names in All Databases
use master
go
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
–where [DB Name] = ‘FileStreamDB’ –Option To Select One Database Or More As you Like
ORDER BY [DB Name],[SCHEMA NAME]
drop table AllDBSchemas

——————————————————————————————-
–List of All Tables in All Databases
use MASTER
GO
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
–WHERE [DB NAME] = ‘YOUR_DATABASE NAME’ — OPTION
ORDER BY [DB Name],[SCHEMA NAME], [TABLE Name]
drop table AllTables
—————————
-2
USE MASTER
GO
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)
SELECT * FROM #AllTables
–WHERE [DB NAME] = ‘YOUR DATABASE NAME’ — OPTION
ORDER BY [DB Name],[SCHEMA NAME], [TABLE Name]
—————————————————————————————–
–LIST OF ALL procedures IN ALL DATABASES
IF OBJECT_ID(‘TempDB..#SPList’,’U’) IS NOT NULL DROP TABLE #SPList
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
–WHERE [DB NAME] = ‘YOUR DATABASE NAME’ — OPTION
ORDER BY [DB Name], [SP Name]
————————————————————–
–LIST OF ALL VIEWS IN ALL DATABASES
IF OBJECT_ID(‘TempDB..#SPList’,’U’) IS NOT NULL DROP TABLE #SPList
CREATE TABLE #SPList ([DB Name] sysname, [SP Name] sysname,
create_date DATETIME, modify_date DATETIME)

EXECUTE (@SQL)

SELECT * FROM #SPList
–WHERE [DB NAME] = ‘YOUR DATABASE NAME’ — OPTION
ORDER BY [DB Name], [SP Name]
——————————————————————————
–Size Tales in Database Return(Count Of Rows,Reserved Size,Data Size,Index Size,UnUsed Size)
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
————————————————————————————————
–Database File Size (MDF.LDF.NDF)in ALL Databases
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]
Drop Table #FileSizes
———————————————–
-2
use master
go
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]
drop table #FileSizes
——————————————————————————————–
—Database SIZE
DECLARE @SQL1 VARCHAR(MAX)
SELECT @SQL1 =COALESCE(@SQL1 + 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 (@SQL1)
——————————-
–2
/*
The first select statement is to get how many kilobytes a data page has.
SQL Server allocates disk space in the unit of data page. Currently each
SQL server data page contains 8k bytes. The number of data pages allocated
to each database file is recorded in the sysfiles system table.
With this information on hand the script creates a temporary table #tem and
update the temporary table with size information which is gathered by querying the sysfiles table.
*/
use master

declare @PageSize varchar(10)
select @PageSize= v.low/1024.0
from master..spt_values v
where v.number=1 and v.type=’E’
–select * from master..spt_values v
select name as DatabaseName, convert(float,null) as Size
into #tem
From sysdatabases

declare @SQL varchar (8000)
set @SQL=”

while exists (select * from #tem where size is null)
begin
select @SQL=’update #tem set size=(select round(sum(size)*’+@PageSize+’/1024,0) From ‘+quotename(databasename)+’.dbo.sysfiles) where databasename=”’+databasename+””
from #tem
where size is null
exec (@SQL)
end

select * from #tem order by DatabaseName
drop table #tem

————————————————————————-
—Backup All Databases with Compression (SQL 2008)
DECLARE @ToExecute VARCHAR(8000)

SELECT @ToExecute = COALESCE(@ToExecute + ‘Backup Database ‘ + QUOTENAME([Name]) +
‘ To Disk = ”E:\Backups\’ + [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)
———————————————————————————————