Posted by Fran on 04/10/07 17:08

Okay,
I've gotten the Loop working..
>>Code

INSERT INTO t_fo_databases
SELECT name FROM sys.databases WHERE name like '%SITE' ORDER BY name

DECLARE @DBs as varchar(MAX)
WHILE (SELECT TOP 1 names FROM t_fo_databases) LIKE '%SITE'

BEGIN
SET @DBs = (SELECT TOP 1 names FROM t_fo_databases)
PRINT @DBs
USE [master]
DELETE FROM t_fo_Databases WHERE names = @DBs
END

>>Returns

(4 row(s) affected)
BSCColla002_SITE

(1 row(s) affected)
BSCColla1_SITE

(1 row(s) affected)
BSCITPor1_SITE

(1 row(s) affected)
sps2007t1_SITE

(1 row(s) affected)


Now if I add:
USE @DBs
SELECT * FROM sys.tables WHERE type = 'U'

I get:
Incorrect syntax near '@DBs'.

Any thoughts?
Thank you..

• Next in forum: Sum of Column of Blob type
• Prev in forum: Re: select distinct record only if certain column not null
• Thread view: Re: Retrieve ALL tables from ALL Databases

[Reply to this message]