StrAll - Helps to construct SQL Statement... Ultimately save the times
StrAll <TableName>
CREATE Procedure StrAll
(@obj varchar(255))
as
declare @fldname varchar(255), @len int, @datatype varchar(50), @nullable bit
declare @sqlUpdate varchar(4000)
declare @sqlDeclare varchar(4000)
declare @sqlInsertFld varchar(4000)
declare @sqlInsertVal varchar(4000)
declare @sqlWhere varchar(4000)
declare @sqlSelect varchar(4000)
declare @first int
DECLARE obj_cursor CURSOR FOR
select name , length, ( select max(name) from systypes where xtype = c.xtype) datatype, isnullable
from syscolumns c where id=object_id(@obj)
order by colorder
DECLARE obj_cursor_pk CURSOR FOR
SELECT syscolumns.name + ' = @' + syscolumns.name
FROM sysindexes, syscolumns, sysindexkeys
WHERE syscolumns.colid = sysindexkeys.colid
and syscolumns.id = sysindexes.id
and sysindexkeys.id = sysindexes.id
and syscolumns.id=object_id(@obj)
and sysindexes.indid = 1 and sysindexkeys.indid=1
begin
select @first = 1, @sqlUpdate = 'UPDATE ' + @obj + ' SET ', @sqlDeclare = 'declare ',
@sqlInsertFld = ' INSERT INTO ' + @obj + ' (', @sqlInsertVal = ') VALUES (', @sqlSelect = ' SELECT '
OPEN obj_cursor
FETCH NEXT FROM obj_cursor INTO @fldname, @len, @datatype, @nullable
WHILE @@FETCH_STATUS = 0
BEGIN
if @first = 1
begin
select @first = 0
end
else
begin
set @sqlDeclare = @sqlDeclare + ', '
set @sqlUpdate = @sqlUpdate + ','
set @sqlInsertFld = @sqlInsertFld + ','
set @sqlInsertVal = @sqlInsertVal + ','
set @sqlSelect = @sqlSelect + ', '
end
set @sqlDeclare = @sqlDeclare + '@' + @fldname + ' ' + @datatype + '(' + ltrim(rtrim(@len)) + ')'
set @sqlInsertFld = @sqlInsertFld + @fldname
set @sqlInsertVal = @sqlInsertVal + '@' + @fldname
set @sqlSelect = @sqlSelect + @fldname
set @sqlUpdate = @sqlUpdate + ' ' + @fldname + '= @' + @fldname
FETCH NEXT FROM obj_cursor INTO @fldname, @len, @datatype, @nullable
END
CLOSE obj_cursor
DEALLOCATE obj_cursor
set @first = 1
set @sqlWhere = ' WHERE '
OPEN obj_cursor_pk
FETCH NEXT FROM obj_cursor_pk INTO @fldname
WHILE @@FETCH_STATUS = 0
BEGIN
if @first = 1
begin
set @first = 0
end
else
begin
set @sqlWhere = @sqlWhere + ', '
end
set @sqlWhere = @sqlWhere + ' ' + @fldname
FETCH NEXT FROM obj_cursor_pk INTO @fldname
END
CLOSE obj_cursor_pk
DEALLOCATE obj_cursor_pk
set @sqlInsertVal = @sqlInsertFld + @sqlInsertVal + ' )'
set @sqlSelect = @sqlSelect + ' FROM ' + @obj
select @sqlDeclare = Replace(@sqlDeclare,'int(4)','int')
print ''
print 'Select........'
print @sqlSelect
print ''
print 'Declare........'
print @sqlDeclare
print ''
print 'Insert........'
print @sqlInsertVal
print ''
print 'Update........'
print @sqlUpdate + @sqlWhere
print ''
print 'Delete........'
print 'DELETE FROM ' + @obj + ' ' + @sqlWhere
END
GO