CREATE FUNCTION dbo.GetColumnsToCopy (@Table AS NVARCHAR(100))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Column VARCHAR(255)
DECLARE @Result VARCHAR(8000)
DECLARE Source CURSOR FOR
SELECT TOP 100 PERCENT '[' + dbo.syscolumns.NAME + '], ' AS [Column]
FROM dbo.syscolumns INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.NAME = @Table) AND (dbo.syscolumns.colstat <> 1) AND (dbo.syscolumns.NAME NOT IN ('PageID', 'AddedOn',
'AddedBy', 'ChangedOn', 'ChangedBy', 'LockedOn', 'LockedBy', 'boaGUID', 'boaStatus'))
ORDER BY dbo.syscolumns.colorder
OPEN Source
FETCH Source INTO @Column
SET @Result = ''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Result = @Result + @Column
FETCH Source INTO @Column
END
CLOSE Source
DEALLOCATE Source
RETURN @Result
END
CREATE FUNCTION dbo.GetDataTypeDeclaration (
@DataType VARCHAR(250),
@CharacterLength INT,
@NumericPrecision INT,
@NumericScale INT
)
RETURNS VARCHAR(250) AS
BEGIN
DECLARE @Declaration VARCHAR(250)
SELECT @Declaration = CASE @DataType
WHEN 'varchar' THEN 'VARCHAR(' + CAST(@CharacterLength AS VARCHAR(50)) + ')'
WHEN 'nvarchar' THEN 'NVARCHAR(' + CAST(@CharacterLength AS VARCHAR(50)) + ')'
WHEN 'char' THEN 'CHAR(' + CAST(@CharacterLength AS VARCHAR(50)) + ')'
WHEN 'nchar' THEN 'NCHAR(' + CAST(@CharacterLength AS VARCHAR(50)) + ')'
WHEN 'text' THEN 'TEXT'
WHEN 'ntext' THEN 'NTEXT'
WHEN 'int' THEN 'INT'
WHEN 'integer' THEN 'INT'
WHEN 'decimal' THEN 'DECIMAL(' + CAST(@NumericPrecision AS VARCHAR(50)) + ', ' + CAST(@NumericScale AS VARCHAR(50)) + ')'
WHEN 'numeric' THEN 'NUMERIC(' + CAST(@NumericPrecision AS VARCHAR(50)) + ', ' + CAST(@NumericScale AS VARCHAR(50)) + ')'
WHEN 'float' THEN 'FLOAT(' + CAST(@NumericPrecision AS VARCHAR(50)) + ')'
WHEN 'real' THEN 'REAL(' + CAST(@NumericPrecision AS VARCHAR(50)) + ')'
ELSE @DataType
END
RETURN @Declaration
END