i wanted to figure out a way to check whether failed or successful to update the operation as successfully or fails. it's simple all you have to do is read the exit code.
bcp.exe will return 1 if it fails, otherwise 0
Monday, August 20, 2007
Friday, August 10, 2007
C# Language Specification
C# Language Specification, Version 3.0 Available for Review
http://download.microsoft.com/download/3/8/8/388e7205-bc10-4226-b2a8-75351c669b09/CSharp%20Language%20Specification.doc
http://download.microsoft.com/download/3/8/8/388e7205-bc10-4226-b2a8-75351c669b09/CSharp%20Language%20Specification.doc
How to create a stagging table ?
special thanks to Robert W. Marda
sp_CreateAndExecTableScript 'InterfaceTree', '_stagging', 0 , 1
CREATE PROCEDURE spCOMMS_CreateStaggingTable
(@TableName varchar(255) = '',
@TableNameExt varchar(255) = '',
@DisplayScript bit = 0,
@Exec bit = 0,
@NoPK bit = 0,
@PKOnly bit = 0,
@NoIndexes bit = 0,
@NoTable bit = 0
)AS
SET NOCOUNT ON
--Begin invalid entries for parameters section
--Test for empty entry
IF @TableName = ''
BEGIN
PRINT '@TableName is a required parameter.'
RETURN 1
END
--Test for source table
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
--End invalid entries for parameters section
DECLARE @Query varchar (8000),
@DFQuery varchar(8000)
SET @Query = ''
SET @DFQuery = ''
--Begin building datetime value to be used to ensure primary key and index names are unique
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)
--End building datetime value
--Begin creating temp tables
--temp table #TableScript is used to gather data needed to generate script that will create the table
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
)
--temp table #IndexScript is used to gather data needed to generate script that will create indexes for table
CREATE TABLE #IndexScript (
IndexName varchar (255),
IndId int,
ColumnName varchar (255),
IndKey int,
UniqueIndex int
)
--End creating temp tables
--Begin filling temp table #TableScript
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 --This criteria used because Enterprise Manager delivers the length in parenthesis for these datatypes when using its scripting capabilities.
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 --the first three joins get column names, data types, and column nullability.
LEFT JOIN sysobjects d ON c.cdefault = d.id --this left join gets column default constraint names.
LEFT JOIN syscomments cm ON cm.id = d.id --this left join gets default values for default constraints.
LEFT JOIN sysindexes g1 ON g1.id = o.id --the left join for sysfilegroups and sysindexes with aliases g1 and g1a
LEFT JOIN sysfilegroups g1a ON g1.groupid = g1a.groupid --are for determining which file group the table is in.
WHERE o.name = @TableName
AND g1.id = o.id AND g1.indid in (0, 1) --these two conditions are to isolate the file group of the table.
END
--End filling temp table #TableScript
--Begin building create table and default value constraints scripts.
IF @NoTable = 0 AND @PKOnly = 0
BEGIN
SET @Query = 'if exists (select * from sysobjects where id = object_id(N' + '''[dbo].['
+ @TableName + @TableNameExt + ']''' + ') and OBJECTPROPERTY(id, N' + '''IsUserTable''' + ') = 1)'
+ CHAR(10) + 'drop table [dbo].[' + @TableName + @TableNameExt + ']'
+ CHAR(10) + 'GO'
+ CHAR(10) + CHAR(10) + 'CREATE TABLE [dbo].[' + @TableName + @TableNameExt + '] ('
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 + @TableNameExt + '] WITH NOCHECK ADD'
SET @DFQuery = @DFQuery
+ CHAR(10) + CHAR(9) + 'CONSTRAINT [DF_' + @TableName + @TableNameExt + '_'
+ @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'
SET @Query = @Query + ' NULL'
ELSE
SET @Query = @Query + ' NOT NULL'
FETCH NEXT FROM ColumnName INTO @ColumnName
IF @@fetch_status = 0
SET @Query = @Query + ', '
END
CLOSE ColumnName
DEALLOCATE ColumnName
SET @Query = @Query
+ CHAR(10) + ')'
--Assign file group name
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
--End building create table and default value constraints scripts.
--Begin filling temp table #IndexScript.
INSERT INTO #IndexScript (IndexName, IndId, ColumnName, IndKey, UniqueIndex)
SELECT i.name,
i.indid,
c.name,
k.keyno,
(i.status & 2) --Learned this will identify a unique index from sp_helpindex
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 --eliminates non indexes
AND LEFT(i.name,7) <> '_WA_Sys' --eliminates statistic indexes
--End filling temp table #IndexScript.
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'
--Begin creating primary key script.
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 + @TableNameExt + '] WITH NOCHECK ADD'
+ CHAR(10) + 'CONSTRAINT [PK_' + @TableName + @TableNameExt + @PK + '_' + @DateTime + '] PRIMARY KEY '
IF @IndID = 1
SET @Query = @Query
+ 'CLUSTERED'
ELSE
SET @Query = @Query
+ 'NONCLUSTERED'
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) + ')'
--Add file group name
IF @GroupName is not null
SET @Query = @Query
+ ' ON [' + @GroupName + ']'
SET @Query = @Query
+ CHAR(10) + 'GO'
END
END
--End creating primary key script.
--Add default value constraint script to main script.
IF @NoTable = 0 AND @PKOnly = 0
SET @Query = @Query
+ @DFQuery
+ CHAR(10) + 'GO'
--Begin building index script.
IF @NoIndexes = 0 AND @PKOnly = 0
BEGIN
IF @NoPK = 0
SET @Query = @Query
+ CHAR(10)
IF @NoTable = 1
SET @Query = @Query
+ '--Add Indexes' + CHAR(10)
ELSE
SET @Query = @Query
+ CHAR(10)
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 + @TableNameExt + ']('
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 + ')'
--Add file group name
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
--End building index script.
DROP TABLE #IndexScript
IF @NoTable = 0 AND @PKOnly = 0
DROP TABLE #TableScript
IF @DisplayScript = 1
PRINT @Query
IF @Exec = 1
BEGIN
--This code needed to remark out all GO commands before executing the code in the variable @Query
SET @Query = REPLACE(@Query,CHAR(10) + 'GO', CHAR(10) + '--GO')
Exec (@Query)
END
RETURN 0
GO
sp_CreateAndExecTableScript 'InterfaceTree', '_stagging', 0 , 1
CREATE PROCEDURE spCOMMS_CreateStaggingTable
(@TableName varchar(255) = '',
@TableNameExt varchar(255) = '',
@DisplayScript bit = 0,
@Exec bit = 0,
@NoPK bit = 0,
@PKOnly bit = 0,
@NoIndexes bit = 0,
@NoTable bit = 0
)AS
SET NOCOUNT ON
--Begin invalid entries for parameters section
--Test for empty entry
IF @TableName = ''
BEGIN
PRINT '@TableName is a required parameter.'
RETURN 1
END
--Test for source table
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
--End invalid entries for parameters section
DECLARE @Query varchar (8000),
@DFQuery varchar(8000)
SET @Query = ''
SET @DFQuery = ''
--Begin building datetime value to be used to ensure primary key and index names are unique
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)
--End building datetime value
--Begin creating temp tables
--temp table #TableScript is used to gather data needed to generate script that will create the table
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
)
--temp table #IndexScript is used to gather data needed to generate script that will create indexes for table
CREATE TABLE #IndexScript (
IndexName varchar (255),
IndId int,
ColumnName varchar (255),
IndKey int,
UniqueIndex int
)
--End creating temp tables
--Begin filling temp table #TableScript
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 --This criteria used because Enterprise Manager delivers the length in parenthesis for these datatypes when using its scripting capabilities.
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 --the first three joins get column names, data types, and column nullability.
LEFT JOIN sysobjects d ON c.cdefault = d.id --this left join gets column default constraint names.
LEFT JOIN syscomments cm ON cm.id = d.id --this left join gets default values for default constraints.
LEFT JOIN sysindexes g1 ON g1.id = o.id --the left join for sysfilegroups and sysindexes with aliases g1 and g1a
LEFT JOIN sysfilegroups g1a ON g1.groupid = g1a.groupid --are for determining which file group the table is in.
WHERE o.name = @TableName
AND g1.id = o.id AND g1.indid in (0, 1) --these two conditions are to isolate the file group of the table.
END
--End filling temp table #TableScript
--Begin building create table and default value constraints scripts.
IF @NoTable = 0 AND @PKOnly = 0
BEGIN
SET @Query = 'if exists (select * from sysobjects where id = object_id(N' + '''[dbo].['
+ @TableName + @TableNameExt + ']''' + ') and OBJECTPROPERTY(id, N' + '''IsUserTable''' + ') = 1)'
+ CHAR(10) + 'drop table [dbo].[' + @TableName + @TableNameExt + ']'
+ CHAR(10) + 'GO'
+ CHAR(10) + CHAR(10) + 'CREATE TABLE [dbo].[' + @TableName + @TableNameExt + '] ('
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 + @TableNameExt + '] WITH NOCHECK ADD'
SET @DFQuery = @DFQuery
+ CHAR(10) + CHAR(9) + 'CONSTRAINT [DF_' + @TableName + @TableNameExt + '_'
+ @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'
SET @Query = @Query + ' NULL'
ELSE
SET @Query = @Query + ' NOT NULL'
FETCH NEXT FROM ColumnName INTO @ColumnName
IF @@fetch_status = 0
SET @Query = @Query + ', '
END
CLOSE ColumnName
DEALLOCATE ColumnName
SET @Query = @Query
+ CHAR(10) + ')'
--Assign file group name
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
--End building create table and default value constraints scripts.
--Begin filling temp table #IndexScript.
INSERT INTO #IndexScript (IndexName, IndId, ColumnName, IndKey, UniqueIndex)
SELECT i.name,
i.indid,
c.name,
k.keyno,
(i.status & 2) --Learned this will identify a unique index from sp_helpindex
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 --eliminates non indexes
AND LEFT(i.name,7) <> '_WA_Sys' --eliminates statistic indexes
--End filling temp table #IndexScript.
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'
--Begin creating primary key script.
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 + @TableNameExt + '] WITH NOCHECK ADD'
+ CHAR(10) + 'CONSTRAINT [PK_' + @TableName + @TableNameExt + @PK + '_' + @DateTime + '] PRIMARY KEY '
IF @IndID = 1
SET @Query = @Query
+ 'CLUSTERED'
ELSE
SET @Query = @Query
+ 'NONCLUSTERED'
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) + ')'
--Add file group name
IF @GroupName is not null
SET @Query = @Query
+ ' ON [' + @GroupName + ']'
SET @Query = @Query
+ CHAR(10) + 'GO'
END
END
--End creating primary key script.
--Add default value constraint script to main script.
IF @NoTable = 0 AND @PKOnly = 0
SET @Query = @Query
+ @DFQuery
+ CHAR(10) + 'GO'
--Begin building index script.
IF @NoIndexes = 0 AND @PKOnly = 0
BEGIN
IF @NoPK = 0
SET @Query = @Query
+ CHAR(10)
IF @NoTable = 1
SET @Query = @Query
+ '--Add Indexes' + CHAR(10)
ELSE
SET @Query = @Query
+ CHAR(10)
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 + @TableNameExt + ']('
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 + ')'
--Add file group name
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
--End building index script.
DROP TABLE #IndexScript
IF @NoTable = 0 AND @PKOnly = 0
DROP TABLE #TableScript
IF @DisplayScript = 1
PRINT @Query
IF @Exec = 1
BEGIN
--This code needed to remark out all GO commands before executing the code in the variable @Query
SET @Query = REPLACE(@Query,CHAR(10) + 'GO', CHAR(10) + '--GO')
Exec (@Query)
END
RETURN 0
GO
Subscribe to:
Posts (Atom)