Presents your 
SQL SERVER E-NEWSLETTER for June 11, 2002

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

DROP "X_TMP" PREFACED DATABASE OBJECTS

Database developers who create database objects prefaced with "x_tmp"
are able to continue development until the database objects are baselined
with standard naming conventions. Baselining refers to putting the
database objects into a source control system like PVCS, Clear Case, etc.

Once the database object code is checked into a source control system,
it can be checked out and run on the development databases. When
determining the need to drop the "x_tmp" database, you can use the script below to
drop "x_tmp" database objects.

This script will create commands for every database to query sysobjects
for "x_tmp" prefaced objects. The objects queried for are tables,
procedures, and views. Commands are built into a temporary table. The temporary
table is traversed and the commands retrieved and executed. The execution
of the command is commented out to preview the output in order to
validate before executing.

SELECT 'select xtype, name, ' + '''' + name + '''' + ' db_nm from ' + name +
'.dbo.sysobjects where name like ' +  '''' + '%x[_]tmp%' +  '''' cmd, 'F' flag
INTO #T1 FROM master..sysdatabases

CREATE TABLE #T2 (xtype CHAR(4) NULL, name VARCHAR(128) NULL, db_nm VARCHAR(128) NULL)

DECLARE @cmd VARCHAR(2000)

WHILE EXISTS ( SELECT 1 FROM #T1 WHERE flag = 'F' )
BEGIN
     SELECT @cmd = cmd FROM #T1 WHERE flag = 'F'
     INSERT #T2
     EXEC (@cmd)
     UPDATE #T1 SET flag = 'T' WHERE cmd = @cmd
END

SELECT CASE xtype
     WHEN 'P' THEN 'USE ' + db_nm + ' DROP PROC ' + name
     WHEN 'U' THEN 'USE ' + db_nm + ' DROP TABLE ' + name
     WHEN 'V' THEN 'USE ' + db_nm + ' DROP VIEW ' + name
     ELSE ' '
     END cmd, 'F' flag INTO #T3 FROM #T2
DELETE #T3 WHERE cmd = ''

WHILE EXISTS ( select 1 from #T3 where flag = 'F' )
BEGIN
     SELECT @cmd = cmd FROM #T3 WHERE flag = 'F'
     SELECT @cmd
--   EXEC (@cmd)
     UPDATE #T3 SET flag = 'T' WHERE cmd = @cmd
END
DROP TABLE #T1, #T2, #T3

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

READ MORE ABOUT DEVELOPMENT

SQL BASICS I: DATA QUERIES
Need a quick overview of SQL? This article will get
you started from
creating basic manipulation queries to altering the
database to more
advanced query concepts.

http://clickthru.online.com/Click?q=61-x9oAIoLQ9_WGYuq4JJe0Fb-SEP4R