--Clone table
--Table must have a primary key
--Does not clone:
--   - Triggers
--   - Text indexes
--   - Comments on PK, Triggers and Text indexes
BEGIN
DECLARE @Sep VARCHAR(2)
SET @Sep = ';'
DECLARE @Table VARCHAR(100)
SET @Table = 'Calendar'
DECLARE @NewTable VARCHAR(100)
SET @NewTable = 'Calendar_new'

SELECT sql FROM (
SELECT '0' as sort, '-- Clone table ' as sql
UNION
SELECT '1', '  -- just comment out the blocks you don''t need.'
UNION
SELECT '2', '-- Creating table'
UNION
--Table
SELECT 'A' as sort, 'CREATE TABLE ' || @NewTable || ' ( '
UNION
  --Columns
SELECT 'B' || sys.syscolumn.object_id, CHAR(9) || '"' || trim(sys.syscolumn.column_name) || 
       '" ' || Upper(trim(sys.sysdomain.domain_name)) || 
       IF sys.syscolumn.nulls = 'N' THEN ' NOT NULL' ELSE ' NULL' END IF ||
       IF sys.syscolumn."default" is null THEN '' ELSE ' DEFAULT ' || Upper(Trim(sys.syscolumn."default")) END IF ||
       ','
   FROM sys.systable,
        sys.syscolumn,
        sys.sysuserperm,
        sys.sysdomain
  WHERE sys.systable.table_id = sys.syscolumn.table_id
    AND sys.systable.creator = sys.sysuserperm.user_id
    and sys.syscolumn.domain_id = sys.sysdomain.domain_id
    AND sys.systable.table_name = @Table
UNION
  --PKEY
SELECT 'C', CHAR(9) || 'PRIMARY KEY ("' || Upper(Trim(sys.syscolumn.column_name)) || '" ASC))' || @Sep
   FROM sys.systable,
        sys.syscolumn,
        sys.sysuserperm,
        sys.sysdomain
  WHERE sys.systable.table_id = sys.syscolumn.table_id
    AND sys.systable.creator = sys.sysuserperm.user_id
    and sys.syscolumn.domain_id = sys.sysdomain.domain_id
    AND sys.systable.table_name = @Table
    AND sys.syscolumn.pkey = 'Y'
UNION
SELECT 'D', '-- Creating indexes'
UNION
--Indexes
SELECT 'D1', 'CREATE INDEX "' || Trim(sys.sysindex.index_name) || '" ON ' || @NewTable || ' ( "' || Trim(sys.sysindex.index_name) || '" ASC )' || @Sep
   FROM sys.systable,
        sys.sysindex
  WHERE sys.systable.table_name = @Table
    AND sys.systable.table_id = sys.sysindex.table_id
UNION
SELECT 'E', '-- Granting permissions'
UNION
--Permissions
SELECT 'E1', 'GRANT ' || IF sys.systableperm.selectauth = 'Y' THEN 'SELECT, ' END IF ||
                        IF sys.systableperm.insertauth = 'Y' THEN 'INSERT, ' END IF ||
                        IF sys.systableperm.deleteauth = 'Y' THEN 'DELETE, ' END IF ||
                        IF sys.systableperm.updateauth = 'Y' THEN 'UPDATE, ' END IF ||
                        IF sys.systableperm.alterauth = 'Y' THEN 'ALTER, ' END IF ||
                        IF sys.systableperm.referenceauth = 'Y' THEN 'REFERENCES , ' END IF ||
                        'ON ' || @NewTable ||
                        ' TO ' || Trim(sys.sysuserperm.user_name) || @Sep
   FROM sys.systable,
        sys.sysuserperm,
        sys.systableperm
  WHERE sys.systable.table_name = @Table
    AND sys.systable.table_id = sys.systableperm.stable_id
    AND sys.systableperm.grantee = sys.sysuserperm.user_id
UNION
SELECT 'F', '-- Putting on comments'
UNION
--Comments on
  --Table
SELECT 'F1','COMMENT ON TABLE ' || @NewTable || ' IS "' || sys.sysremark.remarks || '"' || @Sep
FROM sys.sysremark INNER JOIN sys.sysobject ON sys.sysobject.object_id = sys.sysremark.object_id
INNER JOIN sys.systable ON sys.systable.object_id = sys.sysremark.object_id
WHERE sys.systable.table_name = @Table
UNION
  --Columns
SELECT 'F1','COMMENT ON COLUMN ' || @NewTable || '.' || sys.syscolumn.column_name
|| ' IS "' || sys.sysremark.remarks || '"' || @Sep
FROM sys.sysremark INNER JOIN sys.sysobject ON sys.sysobject.object_id = sys.sysremark.object_id
LEFT JOIN sys.syscolumn ON sys.syscolumn.object_id = sys.sysremark.object_id
WHERE sys.syscolumn.table_id = (select table_id from sys.systable where table_name = @Table) 
UNION
  --Index
SELECT 'F1','COMMENT ON INDEX ' || @NewTable || '.' 
|| sys.sysindex.index_name || ' IS "' || sys.sysremark.remarks || '"' || @Sep
FROM sys.sysremark INNER JOIN sys.sysobject ON sys.sysobject.object_id = sys.sysremark.object_id
LEFT JOIN sys.sysindex ON sys.sysindex.object_id = sys.sysremark.object_id
WHERE sys.sysindex.table_id = (select table_id from sys.systable where table_name = @Table) 
UNION
--Copy data
SELECT 'G', '-- Copying data to new table'
UNION
SELECT 'G0', '  -- As an alternative you could generate SQL insert statements for a result set in ISQL'
UNION
SELECT 'G01', '  -- To generate SQL statements from an Interactive SQL result set'
UNION
SELECT 'G02', '  -- Select the row(s) you want to generate a statement for.'
UNION
SELECT 'G03', '  -- Right-click the selection, and choose Generate, and then choose INSERT Statement, DELETE Statement, or UPDATE Statement.'
UNION
SELECT 'G04', '  -- The statement is copied to the clipboard.'
UNION
SELECT 'G05', '  -- PS: If @NewTable is different from @Table, you will have to search''n replace in the generated sql statements.'
UNION
SELECT 'G1', 'INSERT INTO ' || @NewTable || ' WITH AUTO NAME (SELECT * FROM ' || @Table || ')' || @Sep
Order by sort asc
) Clone
END