With the help of
below sql script we will get the count of tables, sp, function and views in
each database on the server.
Declare @temp table
(ID int identity(1,1),
Name varchar(50))
insert into @temp
select name from master.sys.Databases
declare @min int=1 , @max int
set @max=(select Count(*) from @temp)
declare @Counttbl table
(ID int identity(1,1), DatabaseName varchar(50),
TableCount int,ViewCount int, functionCount int,SPCount int)
while (@min<=@max)
Begin
declare @dbName varchar(50)
declare @s varchar(1000)
set @dbName=(select Name from @temp where id=@Min)
--insert into @Counttbl
set @s=
'Use ' + @dbName +'
SELECT ''
'+@dbName+' ''as DatabaseName,
COUNT(*) AS TABLE_COUNT ,
(SELECT COUNT(*) FROM
INFORMATION_SCHEMA.VIEWS) as Views,
(SELECT COUNT(*) FROM
INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''FUNCTION'' ) AS
FUNCTION_COUNT,
(SELECT COUNT(*) FROM
INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''PROCEDURE'') AS
PROCEDURE_COUNT
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'''
insert into @Counttbl
exec(@s)
set @min=@min+1
End
select * from @Counttbl
|
No comments:
Post a Comment
If you have any doubt, please let me know.