April 18, 2008 at 2:46 pm
I'm looking for a way to count the number of views in each of the databases without having to switch to each one and run a select statement. The following is what I'm trying but it is only returning the view count from master instead of each database. Any ideas?
USE master
DECLARE @DatabaseName VARCHAR(50)
DECLARE @IndexCount VARCHAR(50)
CREATE TABLE #DatabaseOutput
(DatabaseName VARCHAR(50),
[IndexCount] VARCHAR(50))
DECLARE DatabaseList CURSOR FOR
SELECT name
FROM sys.databases
WHERE owner_sid NOT IN (0x01)
ORDER BY name
OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DBString NVARCHAR(500)
SET @DBString = N'USE ' + @DatabaseName
EXECUTE sp_executesql @DBString
SELECT @IndexCount = COUNT(*) FROM sys.indexes WHERE type IN (1,2)
INSERT INTO #DatabaseOutput (DatabaseName, [IndexCount])
VALUES (@DatabaseName, @IndexCount)
FETCH NEXT FROM DatabaseList INTO @DatabaseName
END
CLOSE DatabaseList
DEALLOCATE DatabaseList
SELECT * FROM #DatabaseOutput
ORDER BY DatabaseName
DROP TABLE #DatabaseOutput
April 18, 2008 at 5:10 pm
Your USE command affects only the dynamic sql's connection so you have to do all of your counting in the dynamic sql. Like this:
USE master
DECLARE @DatabaseName VARCHAR(50)
CREATE TABLE #DatabaseOutput
(DatabaseName VARCHAR(50),
[IndexCount] VARCHAR(50))
DECLARE DatabaseList CURSOR FOR
SELECT name
FROM sys.databases
WHERE owner_sid NOT IN (0x01)
ORDER BY name
OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DBString NVARCHAR(500)
SET @DBString = N'USE ' + @DatabaseName + '
DECLARE @IndexCount VARCHAR(50)
SELECT @IndexCount = COUNT(*) FROM sys.indexes WHERE type IN (1,2)
INSERT INTO #DatabaseOutput (DatabaseName, [IndexCount])
VALUES (''' + @DatabaseName + ''', @IndexCount)'
EXECUTE sp_executesql @DBString
FETCH NEXT FROM DatabaseList INTO @DatabaseName
END
CLOSE DatabaseList
DEALLOCATE DatabaseList
SELECT * FROM #DatabaseOutput
ORDER BY DatabaseName
DROP TABLE #DatabaseOutput
April 22, 2008 at 7:04 pm
SELECT @IndexCount = COUNT(*) FROM sys.indexes WHERE type IN (1,2)
I think you want to count number of views in database rather than indexes. Why are you using sys.indexes instead of sys.views?
I think query should be like
USE DB
SELECT @viewCount = Count(*) FROM sys.views
Is this another way to count number of views in db? If so, then how can i display all the views in database?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply