DB Status in SQL Server : Indicates the number of records of all databases objects. (Stored procedure, User Table, Indexes)

DECLARE @db_name AS varchar(100)
DECLARE @sql AS nvarchar(1000)
DROP TABLE #DB_Stats
CREATE TABLE #DB_Stats (
dbname varchar(100),
Description varchar(100),
Total_count int)

DECLARE cur_DB_Names CURSOR FOR
SELECT name FROM master..sysdatabases

open cur_DB_Names

Fetch next from cur_DB_Names into @DB_name

WHILE @@fetch_status =0
begin

SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''',''User_table'', count(*) from [' + @DB_name + ']..sysobjects where xtype = ''U'''
exec sp_executesql @sql
SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''',''indexes'', count(i.name) from [' + @DB_name + ']..sysobjects n join [' + @DB_name + ']..sysindexes i on (n.id = i.id) where n.xtype = ''U'''
exec sp_executesql @sql
SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''' ,''V'',count(*) from [' + @DB_name + ']..sysobjects where xtype = ''V'''
exec sp_executesql @sql
SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''' ,''P'',count(*) from [' + @DB_name + ']..sysobjects where xtype = ''P'''
exec sp_executesql @sql
SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''' ,''DTS'',COUNT(distinct cast(id as varchar(128))) from [msdb]..sysdtspackages'
exec sp_executesql @sql
Fetch next from cur_DB_Names into @DB_name
end

CLOSE cur_DB_Names
deallocate cur_DB_Names

SELECT * FROM #db_stats


Hiç yorum yok:

Visual Studio 2017'de Devexpress 17.2.5 Toolbox görünmüyor

Visual Studio 2017 Toolbox'ı üzerinde Developer Express componentlerini göremiyorsanız aşağıdaki komutu çalıştırmak işini görecektir. (...