Now you can run administrative T-SQL tasks against all your databases in one shot. Very usefull for environments that have a seperate database for each client, ASP's, etc.
It's pretty straight forward and can be used for almost any task. This example Looks for a table and prints the database name and selects from the table if it is found.
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'if exists(select * from @dbname..sysobjects where type = ''u'' and name like ''QueryTable'')' + char(13)
select @isql = @isql + 'begin' + char(13)
select @isql = @isql + 'print ''@dbname''' + char(13)
select @isql = @isql + 'select * from @dbname..QueryTable' + char(13)
select @isql = @isql + 'end' + char(13)
select @isql = replace(@isql,'@dbname',@dbname)
--print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1 
This script will show how quickly update records in one table with information from another or insert records if they are completely new.
2002-04-23
2,199 reads
Detect SQL Server Instance(s) using reg.exe from the resource kit, just pass the host name! (and make sure reg.exe from the resouce kit is in the ../tools/bin directory. SS2k Only.Enjoy!
2002-04-23
1,045 reads
Ever want to display (and edit) ASP code from the original .asp file? (like PHP's PHPInfo() function).To setup: * Just create a .asp with SQL Server connectivity. * Change @IISHostNameWWWPath to match your servername -OR- if SQL Server is on the same machine, use the next line instead. (see […]
2002-04-22
529 reads
This procedure will return useful columns information for VB developers. It shows column name, data type, size, vbtype, checktable (refferenced table) and flags: NoNull, IsIdentity, IsPK.Calling sample: mysp_columns 'customers'
2002-04-19
691 reads
Ever need to write reports out to a folder? I have found that creating output files are SA-WEET! (and easy too) The sample script uses BCP to create an HTML file.This process works well for reports that need to be generated nightly and take too long to run in real time. Use an SMTP mail […]
2002-04-18
4,316 reads