Presents your 
SQL SERVER E-NEWSLETTER for August 6, 2002

<------------------------------------------->

QUERY THE SYSFILES TABLE IN THE MASTER DATABASE

Querying the sysfiles table in the master database can make reattaching
a databases easier. The following is a sample script that will query all
user-defined databases to create a script to reattach the databases.

SET NOCOUNT ON
USE master
GO
CREATE TABLE ##temp_data_files
(   dbname VARCHAR(40)
   ,filed INT
   ,name VARCHAR(80)
   ,filename VARCHAR(254)
   ,flag CHAR(1)
)


SELECT LTRIM(RTRIM(name)) AS 'name', 'F' AS 'flag' INTO #dbs FROM
sysdatabases WHERE name NOT IN
('master','tempdb','model','msdb','pubs','Northwind')ORDER BY dbid
DECLARE   @lv_Ctr         INT
   ,@lv_num_files      INT
   ,@lv_command1      VARCHAR(255)
   ,@lv_command2      VARCHAR(255)
   ,@lv_db_name      VARCHAR(40)
   ,@lv_file_name      VARCHAR(100)

WHILE (SELECT COUNT(*) FROM #dbs WHERE flag = 'F') > 0
BEGIN
   SET ROWCOUNT 1
   SELECT @lv_db_name = name FROM #dbs WHERE flag = 'F'
   SET ROWCOUNT 0

   SELECT @lv_command2 = 'INSERT INTO ##temp_data_files SELECT ''' +
@lv_db_name + ''' AS ''dbname'', fileid, name, filename, ''F'' AS 
''flag'' 
FROM ' + @lv_db_name + '..sysfiles'
   TRUNCATE TABLE ##temp_data_files
   EXECUTE (@lv_command2)

   SELECT @lv_Ctr = 1
   PRINT 'EXEC sp_attach_db @dbname = N''' + @lv_db_name + ''''
   WHILE (SELECT COUNT(*) FROM ##temp_data_files WHERE flag = 'F') > 0
   BEGIN
      SET ROWCOUNT 1
      SELECT @lv_file_name = filename FROM ##temp_data_files WHERE flag 
= 'F'
      SET ROWCOUNT 0

      PRINT ',@filename' + CAST(@lv_Ctr as VARCHAR(2)) + ' = N''' +
@lv_file_name + ''''

      SELECT @lv_Ctr = @lv_Ctr + 1

      UPDATE ##temp_data_files
      SET flag = 'T'
      WHERE dbname = @lv_db_name AND filename = @lv_file_name

   END
   PRINT ''

   UPDATE #dbs
   SET flag = 'T'
   WHERE name = @lv_db_name

END

DROP TABLE #dbs
DROP TABLE ##temp_data_files
SET NOCOUNT OFF

----------------------------------------