테이블 생성 SQL문 만들어보기
--1. PROCEDURE를 하나 만들고..
USE PUBS
GO
IF OBJECT_ID('USP_TABLE_CR_SCRIPT') IS NOT NULL
DROP PROC USP_TABLE_CR_SCRIPT
GO
Create PROCEDURE USP_TABLE_CR_SCRIPT
(
@TableName varchar(255) = '',
@TableNameExt varchar(10) = 'DBO')
AS
/*
RUN EX)
EXEC DBO.USP_TABLE_CR_SCRIPT
'TITLES'
*/
BEGIN
SET NOCOUNT ON
DECLARE @NoPK bit, @PKOnly bit, @NoIndexes bit, @NoTable bit
SELECT
@NoPK
= 0,
@PKOnly
= 0,
@NoIndexes = 0,
@NoTable =
0
IF @TableName = '' BEGIN
PRINT '@TableName is a
required parameter.'
RETURN 1
END
IF NOT EXISTS (select * from sysobjects where
id = object_id(N'[dbo].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN
PRINT 'Table ' + @TableName + ' not
found.'
RETURN 2
END
DECLARE @Query varchar (8000),
@DFQuery varchar(8000)
SET @Query = ''
SET @DFQuery = ''
DECLARE @DateTime
varchar(20)
DECLARE @rawDateTime
varchar(20)
SET @rawdatetime
= CURRENT_TIMESTAMP
SET @DateTime = SUBSTRING(@rawDateTime, 5, 2) + LEFT(@rawDateTime, 3) + SUBSTRING(@rawDateTime, 8, 4)
IF SUBSTRING(@rawDateTime, 13, 1) = ' '
SET @DateTime = @DateTime + SUBSTRING(@rawDateTime, 14, 1)
ELSE
SET @DateTime = @DateTime + SUBSTRING(@rawDateTime, 13, 2)
SET @DateTime = LTRIM(@DateTime) + '_' + SUBSTRING(@rawDateTime,
16, 4)
set @dateTime = ''
IF @NoTable = 0 AND @PKOnly =
0
CREATE TABLE #TableScript (
ColumnName varchar (30),
DataType varchar(40),
Length varchar(4),
[Precision] varchar(4),
Scale varchar(4),
IsNullable varchar(1),
TableName varchar(30),
ConstraintName varchar(255),
DefaultValue varchar (255),
GroupName varchar(35),
collation sysname NULL,
IdentityColumn bit NULL)
CREATE TABLE #IndexScript (
IndexName varchar
(255),
IndId int,
ColumnName varchar
(255),
IndKey int,
UniqueIndex int)
IF @NoTable = 0 AND @PKOnly = 0 BEGIN
INSERT INTO #TableScript (ColumnName, DataType, Length, [Precision], Scale, IsNullable, TableName,
ConstraintName,
DefaultValue,
GroupName,
collation,
IdentityColumn)
SELECT LEFT(c.name,30) AS ColumnName,
LEFT(t.name,30) AS DataType,
CASE t.length
WHEN 8000 THEN c.prec
ELSE NULL
END AS
Length,
CASE t.name
WHEN 'numeric' THEN c.prec
WHEN 'decimal' THEN c.prec
ELSE NULL
END AS
[Precision],
CASE t.name
WHEN 'numeric' THEN c.scale
WHEN 'decimal' THEN c.scale
ELSE NULL
END AS
Scale,
c.isnullable,
LEFT(o.name,30) AS TableName,
d.name AS ConstraintName,
cm.text AS DefaultValue,
g1a.groupname,
c.collation,
CASE
WHEN c.autoval IS NULL THEN 0
ELSE 1
END AS
IdentityColumn
FROM syscolumns
c
INNER JOIN sysobjects o ON c.id = o.id
LEFT JOIN systypes t ON t.xusertype = c.xusertype
LEFT JOIN sysobjects d ON c.cdefault = d.id
LEFT JOIN syscomments cm ON cm.id = d.id
LEFT JOIN sysindexes g1 ON g1.id = o.id
LEFT JOIN sysfilegroups g1a
ON g1.groupid = g1a.groupid
WHERE o.name = @TableName
AND g1.id = o.id AND g1.indid in (0, 1)
END
IF @NoTable = 0 AND @PKOnly = 0 BEGIN
SET @Query = 'if exists (select * from
sysobjects where id = object_id(N' +
'''[dbo].['
+ @TableName + ']''' + ') and OBJECTPROPERTY(id, N' + '''IsUserTable'''
+ ') =
1)'
+ CHAR(10) + 'drop table [dbo].[' + @TableName + ']'
+ CHAR(10) + 'GO'
+ CHAR(10) + CHAR(10) + 'CREATE TABLE [dbo].[' + @TableName + ']
('
DECLARE @DataType
varchar(40),
@Length varchar(4),
@Precision varchar(4),
@Scale varchar(4),
@Isnullable varchar(1),
@DefaultValue varchar(255),
@GroupName varchar(35),
@ColumnName varchar(255),
@ConstraintName varchar(255),
@collation sysname,
@TEXTIMAGE_ON bit,
@IdentityColumn bit
SET @TEXTIMAGE_ON
= 0
DECLARE ColumnName
Cursor For
SELECT ColumnName
FROM #TableScript
OPEN ColumnName
FETCH NEXT FROM ColumnName
INTO @ColumnName
WHILE (@@fetch_status =
0) BEGIN
SELECT
@DataType = DataType,
@Length = Length,
@Precision = [Precision],
@Scale = Scale,
@Isnullable = isnullable,
@DefaultValue =
DefaultValue,
@ConstraintName =
ConstraintName,
@collation = collation,
@IdentityColumn =
IdentityColumn
FROM #TableScript
WHERE ColumnName = @ColumnName
IF @DefaultValue IS
NOT NULL BEGIN
IF @DFQuery = ''
SET @DFQuery = @DFQuery
+ CHAR(10) + CHAR(10) + 'ALTER TABLE
[dbo].[' + @TableName
+ '] WITH
NOCHECK ADD'
SET @DFQuery = @DFQuery
+ CHAR(10) + CHAR(9) + 'CONSTRAINT [DF_'
+ @TableName
+ '_'
+ @ColumnName + '_' + @DateTime + '] DEFAULT ' + @DefaultValue
+ ' FOR [' + @ColumnName + '],'
END
IF @DataType = 'text' OR @DataType = 'ntext'
SET @TEXTIMAGE_ON
= 1
SET @Query = @Query
+ CHAR(10) + CHAR(9) + '[' + @ColumnName + '] [' + @DataType + ']'
IF @IdentityColumn =
1
SET @Query = @Query
+ ' IDENTITY (' + LTRIM(STR(IDENT_SEED(@TableName))) + ', ' + LTRIM(STR(IDENT_INCR(@TableName))) + ')'
IF @DataType = 'varchar' OR @DataType = 'nvarchar' OR @DataType = 'char' OR @DataType = 'nchar'
OR @DataType = 'varbinary' OR @DataType = 'binary'
SET @Query = @Query
+ ' (' + @Length + ')'
IF @DataType = 'numeric' OR @DataType = 'decimal'
SET @Query = @Query
+ ' (' + @Precision + ', ' + @Scale + ')'
IF @collation IS NOT NULL AND @DataType <> 'sysname'
AND @DataType
<> 'ProperName'
SET @Query = @Query
+ ' COLLATE ' + @collation
IF @Isnullable = '1' BEGIN
SET @Query = @Query + '
NULL'
END ELSE BEGIN
SET @Query = @Query + ' NOT
NULL'
END
FETCH NEXT FROM ColumnName INTO
@ColumnName
IF @@fetch_status =
0
SET @Query = @Query + ',
'
END
CLOSE ColumnName
DEALLOCATE ColumnName
SET @Query = @Query + CHAR(10) + ')'
SELECT DISTINCT
@GroupName =
GroupName
FROM #TableScript
IF @GroupName IS NOT NULL
SET @Query = @Query + ' ON [' + @GroupName + ']'
IF @TEXTIMAGE_ON
= 1
SET @Query = @Query + ' TEXTIMAGE_ON [' +
@GroupName +
']'
IF RIGHT(@DFQuery,1) = ','
SET @DFQuery = LEFT(@DFQuery, LEN(@DFQuery) - 1)
SET @Query = @Query + CHAR(10) + 'GO'
END
INSERT INTO #IndexScript (IndexName, IndId, ColumnName, IndKey, UniqueIndex)
SELECT
i.name,
i.indid,
c.name,
k.keyno,
(i.status & 2)
FROM sysindexes
i
INNER JOIN sysobjects o ON i.id = o.id
INNER JOIN sysindexkeys k ON i.id = k.id AND i.indid = k.indid
INNER JOIN syscolumns c ON c.id = k.id AND k.colid = c.colid
WHERE o.name = @TableName
AND i.indid > 0 and i.indid < 255
AND LEFT(i.name,7) <> '_WA_Sys'
DECLARE @PK varchar(2),
@IndID int,
@IndexName varchar(255),
@IndKey int
SET @PK = ''
SET @IndKey = 1
SELECT DISTINCT
@IndexName =
IndexName,
@IndID = indid
FROM #IndexScript
WHERE LEFT (IndexName, 2) = 'PK'
IF @PKOnly = 1 OR (@NoTable = 1 AND @NoPK = 0) BEGIN
SET @Query = '--Add Primary Key'
+ CHAR(10)
SET @PK = 'PK'
END
IF @NoPK = 0 BEGIN
IF @IndexName IS NOT NULL BEGIN
SET @Query = @Query
+ CHAR(10) + CHAR(10) + 'ALTER TABLE
[dbo].[' + @TableName + '] WITH NOCHECK ADD'
+ CHAR(10) + 'CONSTRAINT [PK_'
+ @TableName
+ @PK + '_' + @DateTime + '] PRIMARY KEY '
IF @IndID = 1 BEGIN
SET @Query = @Query + 'CLUSTERED'
END ELSE BEGIN
SET @Query = @Query + 'NONCLUSTERED'
END
SET @Query = @Query + CHAR(10) + '('
DECLARE @OldColumnName varchar(255)
SET @OldColumnName =
'none_yet'
WHILE @IndKey <= 16
BEGIN
SELECT @ColumnName
= ColumnName
FROM #IndexScript
WHERE IndexName
= @IndexName
AND IndID
= @IndID AND IndKey = @IndKey
IF @ColumnName IS NOT NULL AND @ColumnName <>
@OldColumnName
BEGIN
SET @Query = @Query
+ CHAR(10) + '[' + @ColumnName + '],'
END
SET @OldColumnName
= @ColumnName
SET @IndKey = @IndKey + 1
END
IF RIGHT(@Query,1) = ','
SET @Query = LEFT(@Query, LEN(@Query) - 1)
SET @Query = @Query + CHAR(10) + ')'
IF @GroupName is not null
SET @Query = @Query + ' ON [' + @GroupName + ']'
SET @Query = @Query + CHAR(10) + 'GO'
END
END
IF @NoTable = 0 AND @PKOnly =
0
SET @Query = @Query + @DFQuery + CHAR(10) + 'GO'
IF @NoIndexes = 0 AND @PKOnly = 0 BEGIN
IF @NoPK = 0
SET @Query = @Query + CHAR(10)
IF @NoTable = 1 BEGIN
SET @Query = @Query + '--Add Indexes'
+ CHAR(10)
END ELSE BEGIN
SET @Query = @Query + CHAR(10)
END
DECLARE @IndexNameOrig varchar(255),
@UniqueIndex int
DECLARE IndexName
Cursor For
SELECT DISTINCT
IndexName,
indid,
UniqueIndex
FROM #IndexScript
WHERE LEFT (IndexName, 2) <> 'PK' AND LEFT(IndexName, 4) <> 'hind'
OPEN IndexName
FETCH NEXT FROM IndexName INTO @IndexName, @IndID, @UniqueIndex
WHILE @@fetch_status =
0 BEGIN
SET @IndexNameOrig =
@IndexName
IF RIGHT(@IndexName,2) = 'PM' OR RIGHT(@IndexName,2) = 'AM'
SET @IndexName = LEFT(@IndexName, LEN(@IndexName) - 5)
IF LEFT(RIGHT(@IndexName,10),1) = '_'
SET @IndexName = LEFT(@IndexName, LEN(@IndexName) - 10)
ELSE
IF LEFT(RIGHT(@IndexName,11),1) = '_'
SET @IndexName = LEFT(@IndexName, LEN(@IndexName) - 11)
ELSE
IF LEFT(RIGHT(@IndexName,12),1) = '_'
SET @IndexName = LEFT(@IndexName, LEN(@IndexName) - 12)
SET @Query = @Query + CHAR(10) + 'CREATE '
IF @IndID =
1
SET @Query = @Query + 'CLUSTERED '
IF @UniqueIndex <> 0
SET @Query = @Query + 'UNIQUE '
SET @Query = @Query + 'INDEX [' + @IndexName + '_' + @DateTime + '] ON [dbo].[' +
@TableName +
']('
SET @IndKey =
1
SET @OldColumnName =
'none_yet'
WHILE @IndKey <= 16
BEGIN
SELECT @ColumnName
= ColumnName
FROM #IndexScript
WHERE IndexName
= @IndexNameOrig AND
IndID = @IndID AND IndKey = @IndKey
IF @ColumnName IS NOT NULL AND @ColumnName <>
@OldColumnName
BEGIN
SET @Query = @Query
+ '[' + @ColumnName + '],'
END
SET @OldColumnName
= @ColumnName
SET @IndKey = @IndKey + 1
END
IF RIGHT(@Query,1) = ','
SET @Query = LEFT(@Query, LEN(@Query) - 1)
SET @Query = @Query + ')'
IF @GroupName is not null
SET @Query = @Query + ' ON [' + @GroupName + ']'
SET @Query = @Query + CHAR(10) + 'GO' + CHAR(10)
FETCH NEXT FROM IndexName INTO @IndexName, @IndID, @UniqueIndex
END
CLOSE IndexName
DEALLOCATE IndexName
END
print @Query
RETURN 0
END
--2. 실행 및 실행 결과
EXEC DBO.USP_TABLE_CR_SCRIPT
'TITLES'
/*
if exists (select * from sysobjects where id =
object_id(N'[dbo].[TITLES]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[TITLES]
GO
CREATE TABLE [dbo].[TITLES]
(
[title_id]
[tid] COLLATE Korean_Wansung_CI_AS NOT NULL,
[title]
[varchar] (80) COLLATE Korean_Wansung_CI_AS NOT NULL,
[type]
[char] (12) COLLATE Korean_Wansung_CI_AS NOT NULL,
[pub_id]
[char] (4) COLLATE Korean_Wansung_CI_AS NULL,
[price]
[money] NULL,
[advance]
[money] NULL,
[royalty]
[int] NULL,
[ytd_sales]
[int] NULL,
[notes]
[varchar] (200) COLLATE Korean_Wansung_CI_AS NULL,
[pubdate]
[datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TITLES] WITH NOCHECK
ADD
CONSTRAINT
[DF_TITLES_type_] DEFAULT ('UNDECIDED') FOR [type],
CONSTRAINT
[DF_TITLES_pubdate_] DEFAULT (getdate()) FOR
[pubdate]
GO
CREATE INDEX [titleind_] ON [dbo].[TITLES]([title]) ON
[PRIMARY]
GO
CREATE CLUSTERED UNIQUE INDEX [UPKCL_] ON
[dbo].[TITLES]([title_id]) ON [PRIMARY]
GO
*/