Need help changing database dynamically

I have the following code. I basically want to dynamically empty all data
from every user table in every user database. I have the code that processe
s
all the databases I want and then gets all of the tables I want. My problem
is using the GO. I can't get this to run by writing USE @.dbname GO or
USE @.dbname
GO
Does anyone have a recommendation? I'm sure I have just been looking at it
to long at this point and it is easy. Thanks in advance.
-- Sydem
begin tran ProcessDataClean
declare @.dbname varchar(255)
declare @.dbtablename varchar(255)
declare @.sql varchar(255)
DECLARE ProcessDataClean_CURSOR CURSOR FOR
select name
from master.dbo.sysdatabases
where dbid > 6 and
name not in ('pubs', 'Northwinds')
order by name
OPEN ProcessDataClean_CURSOR
FETCH NEXT FROM ProcessDataClean_CURSOR INTO @.dbname
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
select @.sql = 'USE ' + @.dbname
exec sp_sqlexec @.sql
--i want to use GO right here to switch the database
--for now i'm just printing what i want to do
PRINT ''
PRINT @.sql
PRINT 'GO'
PRINT ''
DECLARE GetDBTableName_CURSOR CURSOR FOR
select name
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
order by name
OPEN GetDBTableName_CURSOR
FETCH NEXT FROM GetDBTableName_CURSOR INTO @.dbtablename
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'DELETE ' + @.dbtablename
END
FETCH NEXT FROM GetDBTableName_CURSOR INTO @.dbtablename
END
CLOSE GetDBTableName_CURSOR
DEALLOCATE GetDBTableName_CURSOR
END
FETCH NEXT FROM ProcessDataClean_CURSOR INTO @.dbname
END
CLOSE ProcessDataClean_CURSOR
DEALLOCATE ProcessDataClean_CURSOR
commit tran ProcessDataClean[USE] does not accept a parameter / variable. You will have to execute the
rest of the code inside the context where you are suing "use database_name".
Example:
exec sp_msforeachdb '
if ''?'' not in (
''master'',
''msdb'',
''model'',
''tempdb'',
''pubs'',
''northwind''
)
begin
use [?]
select
table_catalog,
table_name
from
information_schema.tables
end
'
This is an example (may be a bad one), i am not recommending to use the
undocumented and unsupported sp sp_msforeachdb.
Anyway if you try to delete from tables being referenced by other tables,
you will get an error. I think it will be better if you drop the databases
and recreate them.
AMB
"Sydem" wrote:

> I have the following code. I basically want to dynamically empty all data
> from every user table in every user database. I have the code that proces
ses
> all the databases I want and then gets all of the tables I want. My probl
em
> is using the GO. I can't get this to run by writing USE @.dbname GO or
> USE @.dbname
> GO
> Does anyone have a recommendation? I'm sure I have just been looking at i
t
> to long at this point and it is easy. Thanks in advance.
> -- Sydem
>
> begin tran ProcessDataClean
> declare @.dbname varchar(255)
> declare @.dbtablename varchar(255)
> declare @.sql varchar(255)
> DECLARE ProcessDataClean_CURSOR CURSOR FOR
> select name
> from master.dbo.sysdatabases
> where dbid > 6 and
> name not in ('pubs', 'Northwinds')
> order by name
> OPEN ProcessDataClean_CURSOR
> FETCH NEXT FROM ProcessDataClean_CURSOR INTO @.dbname
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> select @.sql = 'USE ' + @.dbname
> exec sp_sqlexec @.sql
> --i want to use GO right here to switch the database
> --for now i'm just printing what i want to do
> PRINT ''
> PRINT @.sql
> PRINT 'GO'
> PRINT ''
> DECLARE GetDBTableName_CURSOR CURSOR FOR
> select name
> from dbo.sysobjects
> where OBJECTPROPERTY(id, N'IsUserTable') = 1
> order by name
> OPEN GetDBTableName_CURSOR
> FETCH NEXT FROM GetDBTableName_CURSOR INTO @.dbtablename
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT 'DELETE ' + @.dbtablename
> END
> FETCH NEXT FROM GetDBTableName_CURSOR INTO @.dbtablename
> END
> CLOSE GetDBTableName_CURSOR
> DEALLOCATE GetDBTableName_CURSOR
> END
> FETCH NEXT FROM ProcessDataClean_CURSOR INTO @.dbname
> END
> CLOSE ProcessDataClean_CURSOR
> DEALLOCATE ProcessDataClean_CURSOR
> commit tran ProcessDataClean|||Correction,

> Anyway if you try to delete from tables being referenced by other tables,
> you will get an error.
Anyway, if you try to delete from tables being referenced by other tables
and there are rows related in the child table, you will get an error.
Example:
exec ('use northwind delete [dbo].[orders]')
go
AMB
"Alejandro Mesa" wrote:
> [USE] does not accept a parameter / variable. You will have to execute the
> rest of the code inside the context where you are suing "use database_name
".
> Example:
> exec sp_msforeachdb '
> if ''?'' not in (
> ''master'',
> ''msdb'',
> ''model'',
> ''tempdb'',
> ''pubs'',
> ''northwind''
> )
> begin
> use [?]
> select
> table_catalog,
> table_name
> from
> information_schema.tables
> end
> '
> This is an example (may be a bad one), i am not recommending to use the
> undocumented and unsupported sp sp_msforeachdb.
> Anyway if you try to delete from tables being referenced by other tables,
> you will get an error. I think it will be better if you drop the databases
> and recreate them.
>
> AMB
> "Sydem" wrote:
>|||Great Alejandro. I will take a look at that.
"Alejandro Mesa" wrote:
> Correction,
>
> Anyway, if you try to delete from tables being referenced by other tables
> and there are rows related in the child table, you will get an error.
> Example:
> exec ('use northwind delete [dbo].[orders]')
> go
>
> AMB
> "Alejandro Mesa" wrote:
>