February 5, 2009 at 3:40 am
schema and user's calculation done in last two row
DECLARE @DBNAME VARCHAR(50)
SET @DBNAME = 'rtemp'
DECLARE @Rsql VARCHAR(MAX)
SET @Rsql = '
SELECT (SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE = ''U'' AND TYPE <> ''S'') AS TABLE_COUNT,
(SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE = ''P'' AND TYPE <> ''S'') AS SP_COUNT,
(SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE_DESC LIKE ''%FUNCTION%'' AND TYPE <> ''S'') AS FUNCTION_COUNT,
(SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE = ''V'' AND TYPE <> ''S'') AS VIEW_COUNT,
(SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE = ''TR'' AND TYPE <> ''S'')AS TRIGGER_COUNT,
(SELECT COUNT(*) FROM '+@DBNAME+'.SYS.SYSUSERS WHERE UID 0) AS USER_COUNT,--this row for usercount
(SELECT COUNT(*) FROM '+@DBNAME+'.sys.schemas WHERE SCHEMA_ID < 16384) AS SCHEMA_COUNT -- this row is for schema count
'
PRINT @Rsql
EXEC (@rsql)
Raj Acharya
February 5, 2009 at 11:04 am
Hi
Not qute sue why you are making it so complex.
SELECT COUNT(*),type_desc FROM sys.objects
GROUP BY type_desc
use this query and copy the output to Excel sheet and do the cosmetic changes.
rather trying to put it in the following format..
No.Of Tables : 10 No.Of view : 20
No.Of Functions : 10 No.Of procedures : 20
This is not a good format to read the data.
The above query output format is more elegant:hehe:
Thanks -- Vj
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply