following info about all the dbs on the server at one go

Question

Viewing 4 posts - 1 through 3 (of 3 total)

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

Kishore-132325 Hall of Fame Points: 3784 More actions · Answer 1

You will get the maximum information in these 2 tables.

select * from sysdatabases

select * from sysaltfiles

--Kishore

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 2

This is how I'd solve it : (be aware of collation conflicts !)

print 'Fillocations of DB'

print '------------------'

declare @SelStmt as varchar(7000)

declare @DbNaam as varchar(125)

declare @UnionStmt as varchar(10)

select @SelStmt = ''

select @UnionStmt = ''

-- select 'select '+ name +' as DBname , * from '+name+'.dbo.sysfiles ' from master.dbo.sysdatabases

declare CsrDB insensitive cursor for select name as DBNaam from master.dbo.sysdatabases for read only

OPEN CsrDB /*open the cursor*/

FETCH NEXT FROM CsrDB INTO @DbNaam /*Get the 1st row*/

WHILE @@fetch_status=0 /*set into loop until no more data can be found*/

BEGIN

IF not @@fetch_status = -2

BEGIN

select @SelStmt = @SelStmt + @UnionStmt + ' Select ''' + ltrim(@DbNaam) +''' as DbNm, * from ' + ltrim(@DbNaam) +'.dbo.sysfiles'

select @UnionStmt = ' Union All '

END

FETCH NEXT FROM CsrDB INTO @DbNaam /* get the next row*/

END

Close CsrDB

DEALLOCATE CsrDB

select @SelStmt = @SelStmt + ' Order by DbNm,groupid,fileid'

exec (@SelStmt )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

emgmg SSC Enthusiast Points: 111 More actions · Answer 3

This link has scripts that can be used to cycle through each database on a server, or each table in a database.