create a view in every db on server

Question

Post reply

create a view in every db on server

Sam Greene

SSCertifiable

Points: 5252
More actions
November 2, 2006 at 10:22 am

#90748

Hi,
I'm doing an audit of indexes on several servers and would like to create a view that i could use in a stored procedure to gather into a temp table. The goal is to find duplicate indexes, and i'm following instructions found in an article on this site. I tried using a procedure, but learned that you can't create views in procedures. then I tried using the undocumented stored procedure for microsoft, sp_MSforeachdb , which can only take 128 characters as it's input. How can i do this, without sitting in query analyzer for hours messing around?
Thanks alot!
sam.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bhushan Default port Points: 1427 More actions · Answer 1

Check the following article by Merrill

http://www.sql-server-performance.com/ma_finding_duplicate_indexes.asp

Sam Greene SSCertifiable Points: 5252 More actions · Answer 2

Sam Greene

SSCertifiable

Points: 5252

November 2, 2006 at 1:09 pm

#669607

thanks, but that is the article i was using.

rcavill Default port Points: 1482 More actions · Answer 3

Here is how to embed the creation of the view within the sp_MSforeachdb proc.

Please note all quotes are single quote character (') that may be repeated up to four times. No double quote characters (") are used in the code below.

/* --------- start script --------- */

sp_MSforeachdb

'

use ?

exec (''

CREATE VIEW vw_index_list AS

SELECT tbl.[name] AS TableName,

idx.[name] AS IndexName,

INDEX_COL( tbl.[name], idx.indid, 1 ) AS col1,

INDEX_COL( tbl.[name], idx.indid, 2 ) AS col2,

INDEX_COL( tbl.[name], idx.indid, 3 ) AS col3,

INDEX_COL( tbl.[name], idx.indid, 4 ) AS col4,

INDEX_COL( tbl.[name], idx.indid, 5 ) AS col5,

INDEX_COL( tbl.[name], idx.indid, 6 ) AS col6,

INDEX_COL( tbl.[name], idx.indid, 7 ) AS col7,

INDEX_COL( tbl.[name], idx.indid, 8 ) AS col8,

INDEX_COL( tbl.[name], idx.indid, 9 ) AS col9,

INDEX_COL( tbl.[name], idx.indid, 10 ) AS col10,

INDEX_COL( tbl.[name], idx.indid, 11 ) AS col11,

INDEX_COL( tbl.[name], idx.indid, 12 ) AS col12,

INDEX_COL( tbl.[name], idx.indid, 13 ) AS col13,

INDEX_COL( tbl.[name], idx.indid, 14 ) AS col14,

INDEX_COL( tbl.[name], idx.indid, 15 ) AS col15,

INDEX_COL( tbl.[name], idx.indid, 16 ) AS col16,

dpages,

used,

rowcnt

FROM sysindexes idx

INNER JOIN sysobjects tbl ON idx.[id] = tbl.[id]

WHERE indid > 0

AND INDEXPROPERTY( tbl.[id], idx.[name], ''''IsStatistics'''') = 0

'' )

if @@error = 0

begin

print ''created view in ?''

end

else

begin

print ''failed to create view in ?'' + str (@@error)

end

'

/* --------- end script --------- */

Then to check that it has been created...

exec sp_MSforeachdb

' use ? select ''?'' [dbname], o.name, o.crdate from sysobjects o where name like ''vw_index_list''

'

Lowell SSC Guru Points: 323518 More actions · Answer 4

can't you just create a view and make the owner INFORMATION_SCHEMAS, so it shows up/is available everywhere? there's several articles and posts that mention doing that.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sam Greene SSCertifiable Points: 5252 More actions · Answer 5

Thanks guys,

I'll look into the INFORMATION_SCHEMA route - seems the best. But thanks for the foreachdb tip too!

Sam