Quick, list the dbs and Users!
January 21, 2008 on 2:21 pm | In SQL Code | No CommentsI’ve been orchestrating a move from an old SQL 2000 database to a SQL 2k5 DB host. Most of the dbs are in use somewhere by some application or other, and I can’t tell by looking at it just what they are. One of the things I’ll need to do is contact all of the users of each one and ask them to tell me what the impact is, as well as inform them that they’ll need to swap a hostname out in the connection string. No problem, I can just click through all 50 databases on the host and look at all the users, and write them down, and send an email to them all asking what gives… The trouble is, half the time they have no idea what db’s are on the host, and what if any they have to do with it. I didn’t feel like spending about 4 hours on this, so I quickly tossed off a script that generates some SQL to figure out the real list, without getting too complicated here. In case you find it useful, here’s the script to generate the second script:
use master
-- below is a query to generate a single query to get all the users for each database
begin
declare dbs cursor for select name from sysdatabases
declare @dbName VARCHAR(25)
open dbs
fetch dbs into @dbName
while @@fetch_status >= 0
begin
if @dbName in ('master', 'msdb', 'tempdb', 'pubs', 'model')
begin
print '-- skipping system db ' + @dbName
end
else
begin
print '----------------------------------'
print '-- ' + @dbName + ' --'
print '-------------------------------------------------------------------'
print 'select ''' + @dbName + ''' as [DB], name as [User] from ' + @dbName + '.dbo.sysusers where hasdbaccess = 1'
print 'union'
end
fetch next from dbs into @dbName
end
close dbs
deallocate dbs
end
go
After that runs it will generate in the output window (or the console if you use OSQL or some other command line tool to run it,) this form of output. I pipe it to another file, and change the last ‘union’ to a ‘go’ and we’re done. I probably should have also excluded the user dbo, but I didn’t you can if you want.
----------------------------------
-- FirstDB --
-------------------------------------------------------------------
select 'FirstDB' as [DB], name as [User] from FirstDB.dbo.sysusers where hasdbaccess = 1
union
----------------------------------
-- aspnetdb --
-------------------------------------------------------------------
select 'aspnetdb' as [DB], name as [User] from aspnetdb.dbo.sysusers where hasdbaccess = 1
union
----------------------------------
-- OtherDB--
-------------------------------------------------------------------
select 'OtherDB' as [DB], name as [User] from OtherDB.dbo.sysusers where hasdbaccess = 1
go -- changed from union as generated by the script
-- this continues until all dbs are enumerated, excludes the system dbs.
Rich Programmer Food: Highly recommended reading
January 18, 2008 on 3:37 pm | In General Programming | No Commentshttp://steve-yegge.blogspot.com/2007/06/rich-programmer-food.html
I have been preaching things like this for a long time, but Steve here is a bit more fluent about explaining it. I’ll not try to turn you off to the ideas presented here by summarizing it before you read it. Honestly, it’s the perfect expression of a relatively amorphous emotion I’ve had about compilers and their place in the world of CS for a long time, (but obviously I can’t take credit for the quality of Steve’s writing, of course.)
Make yourself read it though, please, if you never do another thing for me as long as you know me. (Halo_Four, if you’re reading this, this means you too.)
Powered by WordPress with Pool theme design by Borja Fernandez.
Entries and comments feeds.
Valid XHTML and CSS. ^Top^