Vytvoreni temp tabulky ze schematu existujici – MS SQL – Fórum – Programujte.com

Ahoj 

Ptreboval bych vytvorit temp tabulku dle schematu existujici tabulky. Do teto temp tabulky prekopirovat cely obsah no a pak puvodni smazat a tuto temp prejmenovat dle puvodniho jmena a dalsi praci nad touto prejmenovanou  temp. A to cele pod SQLCMD.

Dostal jsem se az sem ale bouzel nevede se mi poskladat SQL string tak aby byl spravny. Chyba je zejmena ve velikosti typu.

DECLARE @colname sysname
declare @typname varchar(max)
declare @length varchar(max)
declare @precision varchar(max)
DECLARE @collist varchar(max)
SET @collist = 'create table tmp_MOJETABULKA '
DECLARE colCursor CURSOR FOR 
select a.name,t.name,t.length,t.prec
--,c.definition
from 
sys.all_columns a 
inner join sys.tables b on a.object_id = b.object_id
LEFT JOIN  systypes t on t.xtype = a.system_type_id
inner join sys.default_constraints c on a.default_object_id = c.object_id
where 
--t.status=0 and 
b.name='MOJETABULKA'

OPEN colCursor   
FETCH NEXT FROM colCursor INTO @colname,@typname,@length,@precision   
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @collist = @collist + @colname+' ' +@typname+'('+@length+','+@precision+')'
       FETCH NEXT FROM colCursor INTO @colname,@typname,@length,@precision      
       IF @@FETCH_STATUS = 0 
   SET @collist = @collist + ','
END   
CLOSE colCursor   
DEALLOCATE colCursor 

PRINT @collist

--IF EXISTS(SELECT * FROM MOJETABULKA)
--    EXEC('INSERT INTO dbo.Tmp_MOJETABULKA (id, name)
  --    SELECT id, name FROM dbo.MOJETABULKA WITH (HOLDLOCK TA--BLOCKX)')
--GO
--DROP TABLE dbo.MOJETABULKA
--GO
--EXECUTE sp_rename N'dbo.Tmp_MOJETABULKA', N'MOJETABULKA', 'O--BJECT' 
--GO

a vysledek vypada takto

create table tmp_dochazka_typy nazev nvarchar(8000,4000),nazev sysname(256,128),zakazky bit(1,1),cas_od time(5,16),cas_do time(5,16),cas_interval time(5,16),terminal_order int(4,10),ciselnikPraceId int(4,10),autoPrichodOdchod bit(1,1)

primarne nejde o spravne zazavorkovani ale o hodnoty typu a default constraintu ktere take potrebuji doplnit

prosim a dekuji za kazdou radu - pomoc