select * from *.sys possible? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select * from *.sys possible?

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-03 : 18:42:24

i want to say

select all from "alldatabases".sys.indexes

i dont want to use the sp_msforeachdb procedure because it doesnt work with what im trying to do further on, is there a way to do this and not using cursors either.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-03 : 18:47:30

There is no way to do this without looping of some sort or using sp_msforeachdb.

Tara Kizer

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-03 : 19:05:25

okay can i use something like

declare @i int

select @i=0

while @i<3
db_name(@i).sys.indexes
...

is there a way to do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-03 : 19:08:40

There is no difference between a WHILE loop and a cursor. Either way, it'll require dynamic SQL.

Tara Kizer

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-03 : 19:13:16

ah right so performance gains of using a while over a cursor are minimal

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-03 : 19:13:51

also for future reference is it possible at all to put a variable before .sys?
such as

@i.sys.indexes?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-03 : 19:14:46

No

Tara Kizer

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-03 : 19:16:22

thnx

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-03 : 19:18:12

so i take it this is the best way for me to do this:

exec sp_msforeachdb'
use ?
INSERT INTO #minatest
SELECT
db_name(database_id),
phystat.page_count,
i.fill_factor,
OBJECT_NAME(i.object_id),
i.name,
phystat.avg_fragmentation_in_percent,
newfragmentvalue = 0
FROM sys.dm_db_index_physical_stats(NULL,NULL, NULL, NULL, NULL) phystat
JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id
WHERE phystat.avg_fragmentation_in_percent > 10 AND phystat.page_count < 10000
'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-03 : 20:33:33

select every user db sys.indexes: (note: will fail if any dbs are offline or if u dont have appropriate permissions. can exclude specific offline dbs in where clause of declare cursor)


CREATE TABLE #t(
	db varchar(255),
	object_id int,
	name nvarchar(255),
	index_id int,
	type tinyint,
	type_desc nvarchar(60),
	is_unique bit,
	data_space_id int,
	ignore_dup_key bit,
	is_primary_key bit,
	is_unique_constraint bit,
	fill_factor tinyint,
	is_padded bit,
	is_disabled bit,
	is_hypothetical bit,
	allow_row_locks bit,
	allow_page_locks bit
)

Declare @db varchar(255)

Declare c Cursor
read_only
for
	select name from sys.databases where database_id > 4

open c
fetch next from c into @db

while (@@fetch_status = 0)
Begin
	Exec ('INSERT #t
			SELECT ''' + @db + ''', * FROM ' + @db + '.sys.indexes'
	)
	fetch next from c into @db
End
close c
deallocate c

select * from #t
drop table #t