Help modifying insert generation script
-
I have the following script to generate INSERT scripts for
tables in my SQL Server database. I want to enhance this script
to neglect tables with 0 records and to be able to include
a list of tables to skip, i.e tables with an Image type column.
I modified the code shown below, with the lines that are
commented out but I get errors when tryin to run it when the
lines are not commented out.
Server: Msg 137, Level 15, State 2, Line 32
Must declare the variable '@vsTableName'.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the table '#tmp', because it does not exist in the system catalog.
Server: Msg 3701, Level 11, State 5, Line 3
Cannot drop the table '#tmp2', because it does not exist in the system catalog.
Can anyone help out here as my SQL is not that strong.
[sql]
create table #tmp (
SQLText varchar(8000) )
create table #tmp2 (
Id int identity,
SQLText varchar(8000) )
set nocount on
delete #tmp
delete #tmp2
declare @vsSQL varchar(8000),
@vsCols varchar(8000),
@vsTableName varchar(40)
declare csrTables cursor for
select name
from sysobjects
where type in ('u')
order by name
open csrTables
fetch next from csrTables into @vsTableName
-- declare @rowcount int
while (@@fetch_status = 0)
begin
-- SET @rowcount = 0
-- PRINT 'Getting Columns for table:' + @vsTableName
-- select @rowcount = count(*) from @vsTableName
-- IF (@rowcount > 0) AND (@vsTableName <> 'dtproperties')
-- BEGIN
select @vsSQL = '',
@vsCols = ''
select @vsSQL = @vsSQL +
CASE when sc.type in (39,47,61,111) then
'''''''''+' + 'isnull(rtrim(replace('+ sc.name + ','''''''','''''''''''')),'''')' + '+'''''',''+'
when sc.type = 35 then
'''''''''+' + 'isnull(rtrim(replace(substring('+ sc.name + ',1,1000),'''''''','''''''''''')),'''')' +
'+'''''',''+'
else
'isnull(convert(varchar,' + sc.name + '),''null'')+'',''+'
end
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID
select @vsCols = @vsCols + sc.name + ','
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID
select @vsSQL = substring(@vsSQL,1,datalength(@vsSQL)-1)
select @vsCols = substring(@vsCols,1,datalength(@vsCols)-1)
insert #tmp
exec ('select ' + @vsSQL + ' from ' + @vsTableName)
-- PRINT 'Building Insert for table:' + @vsTableName
update #tmp
set sqltext = 'insert ' + @vsTableName + '(' + @vsCols + ') values (' +
substring(sqltext,1,datalength(sqltext)-1) + ')'
insert #tmp2
select 'DELETE from ' + @vsTableName
insert #tmp2 values ('GO')
if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select 'set identity_insert ' + @vsTableName + ' on'
end
insert #tmp2
select * from #tmp
-- END
if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select 'set identity_insert ' + @vsTableName + ' off'
end
insert #tmp2 values ('GO')
insert #tmp2
select 'update statistics ' + @vsTableName
insert #tmp2 values ('GO')
delete #tmp
fetch next from csrTables into @vsTableName
end
close csrTables
deallocate csrTables
update #tmp2
set sqltext = substring(sqltext,1,charindex(',)',sqltext)-1) + ',NULL)'
where not(charindex(',)',sqltext) = 0)
update #tmp2
set sqltext = replace(sqltext, ',''''',',null')
where not (charindex(',''''',sqltext) = 0)
update #tmp2
set sqltext = replace(sqltext, '(''''',',null')
where not (charindex('(''''',sqltext) = 0)
set nocount off
select sqltext from #tmp2 order by id
go
drop table #tmp
drop table #tmp2
[/sql]
-
You'd be better off to use IF EXISTS. It'll return true or false instead of the row count. On small table it's not a big problem but those things add up in the end :
DECLARE @Exists AS INT
DECLARE @TableName AS SYSNAME
DECLARE @sql AS NVARCHAR(1000) --MUST BE UNICODE
SET @TableName = 'SysObjects'
SET @Exists = 0
SET @sql = 'SELECT @Exists = CASE WHEN EXISTS (SELECT * FROM [' + @TableName + ']) THEN 1 ELSE 0 END'
EXEC sp_executesql @sql, N'@Exists INT OUTPUT', @Exists OUTPUT
IF @Exists = 1
BEGIN
PRINT 'EXEC CODE FOR ' + @TableName
END
-
How would I also ignore tables that have an IMAGE type field?
I can't quite get it.
-
Sorry, I had forgotten to answer that one
!DECLARE @Exists AS INT
DECLARE @TableName AS SYSNAME
DECLARE @sql AS NVARCHAR(1000) --MUST BE UNICODE
SET @TableName = 'SysObjects' --works
SET @TableName = 'SysIndexes' --fail for image datatype
SET @Exists = 0
SET @sql = 'SELECT @Exists = CASE WHEN EXISTS (SELECT * FROM [' + @TableName + ']) AND NOT EXISTS (SELECT * FROM dbo.SysObjects O INNER JOIN dbo.SysColumns C ON O.id = C.id WHERE O.Name = ''' + REPLACE(@TableName, '''', '''''') + ''' AND C.XType = 34) THEN 1 ELSE 0 END'
EXEC sp_executesql @sql, N'@Exists INT OUTPUT', @Exists OUTPUT
IF @Exists = 1
BEGIN
PRINT 'EXEC CODE FOR ' + @TableName
END
ELSE
BEGIN
PRINT 'No rows are found or an image column exists'
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply