"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 processes
> 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

"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:
>
> > 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 processes
> > 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

"Alejandro Mesa" wrote:

> 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:
> >
> > > 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 processes
> > > 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