Copy Table Structre using TSQL

It was very tricky question asked to me during one of DBA session and that time my answer was no Idea, but we can do this very easily with select command.

TSQL

select * from into ZFI_TAB_4_DAYBUK2 SAP..MYAC.ZFI_TAB_4_DAYBUK where 1=2

ZFI_TAB_4_DAYBUK2 is new table

ZFI_TAB_4_DAYBUK  is a source table

We can also use SET FMTONLY to view metdata of table or query returns.

SET FMTONLY ON;

GO

SELECT     BUKRS, BELNR, BUZEI, GJAHR, BLART, LTEXT,

CONVERT(datetime,LEFT(BUDAT,4)+SUBSTRING(BUDAT,5,2)+RIGHT(BUDAT,2)) as BUDAT,

CONVERT(datetime,LEFT(BLDAT,4)+SUBSTRING(BLDAT,5,2)+RIGHT(BLDAT,2)) as BLDAT,

BSCHL, GSBER, GTEXT, UMSKZ, DEB_CRE, DMBTR, WAERS, ACC_TYPE,

SGTXT, SAKNR, TXT50, KUNNR, CUST, LIFNR, VEND, XBLNR, TXT60, BUSINESS_AREA,

CONVERT(datetime,LEFT(CPUDT,4)+SUBSTRING(CPUDT,5,2)+RIGHT(CPUDT,2)) as CPUDT,

VEND_CUST_GRP

--into ZFI_TAB_4_DAYBUK2

FROM         ZFI_TAB_4_DAYBUK

GO

SET FMTONLY OFF