March 1, 2005 at 8:34 am
Hi,
This might be a really dumb question but is there a way to do something like this:
Select (select name
From syscolumns
where id = (select id from sysobjects where name = 'mytable')
And name <> 'autonumber')
From mytable
Basically, I need to copy a new record into 'mytable' from data in 'mytable'. I really need something like this:
Insert Into mytable (Select (select name
From syscolumns
where id = (select id from sysobjects where name = 'mytable')
And name <> 'autonumber')
From mytable) Values (Select (select name
From syscolumns
where id = (select id from sysobjects where name = 'mytable')
And name <> 'autonumber' Where autonumber = 10)
The problem is, the column names in my various tables aren't known, they are being created dynamically.
Thanks for any insight you can provide,
Craig
March 1, 2005 at 10:00 am
It's not dumb... but it's actually pretty hard to do.
I got this from microsoft but I lost the original link some time ago... this is my slightly modified version :
USE master
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnTableColumnInfo]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnTableColumnInfo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnColumnDefault]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnColumnDefault]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnCleanDefaultValue]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnCleanDefaultValue]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnIsColumnPrimaryKey]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnIsColumnPrimaryKey]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnTableHasPrimaryKey]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnTableHasPrimaryKey]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pr__SYS_MakeDeleteRecordProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pr__SYS_MakeDeleteRecordProc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pr__SYS_MakeAllRecordProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pr__SYS_MakeAllRecordProc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pr__SYS_MakeForEachTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pr__SYS_MakeForEachTable]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pr__SYS_MakeInsertRecordProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pr__SYS_MakeInsertRecordProc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pr__SYS_MakeSelectRecordProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pr__SYS_MakeSelectRecordProc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pr__SYS_MakeUpdateRecordProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pr__SYS_MakeUpdateRecordProc]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnCleanDefaultValue(@sDefaultValue varchar(4000)) RETURNS varchar(4000) AS BEGIN
RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2) END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnIsColumnPrimaryKey(@sTableName varchar(128), @nColumnName varchar(128))
RETURNS bit
AS
BEGIN
DECLARE @nTableID int,
@nIndexID int,
@i int
SET @nTableID = OBJECT_ID(@sTableName)
SELECT @nIndexID = indid
FROM dbo.sysindexes
WHERE id = @nTableID
AND indid BETWEEN 1 And 254
AND (status & 2048) = 2048
IF @nIndexID Is Null
RETURN 0
IF @nColumnName IN
(SELECT sc.[name]
FROM dbo.sysindexkeys sik
INNER JOIN dbo.syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid
WHERE sik.id = @nTableID
AND sik.indid = @nIndexID)
BEGIN
RETURN 1
END
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnTableHasPrimaryKey(@sTableName varchar(128)) RETURNS bit AS BEGIN
DECLARE @nTableID int,
@nIndexID int
SET @nTableID = OBJECT_ID(@sTableName)
SELECT @nIndexID = indid
FROM dbo.sysindexes
WHERE id = @nTableID
AND indid BETWEEN 1 And 254
AND (status & 2048) = 2048
IF @nIndexID IS NOT Null
RETURN 1
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnColumnDefault(@sTableName varchar(128), @sColumnName
varchar(128))
RETURNS varchar(4000)
AS
BEGIN
DECLARE @sDefaultValue varchar(4000)
SELECT@sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT)
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_NAME = @sTableName
AND COLUMN_NAME = @sColumnName
RETURN @sDefaultValue
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnTableColumnInfo(@sTableName varchar(128))
RETURNS TABLE
AS
RETURN (
SELECTc.name AS sColumnName,
c.colid AS nColumnID,
dbo.fnIsColumnPrimaryKey(@sTableName, c.name) AS bPrimaryKeyColumn,
CASE WHEN t.name IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar') THEN 1
WHEN t.name IN ('decimal', 'numeric') THEN 2
WHEN t.name IN ('text', 'ntext','image') THEN 3
ELSE 0
END AS nAlternateType,
c.length AS nColumnLength,
c.prec AS nColumnPrecision,
c.scale AS nColumnScale,
c.IsNullable,
SIGN(c.status & 128) AS IsIdentity,
t.name as sTypeName,
dbo.fnColumnDefault(@sTableName, c.name) AS sDefaultValue
FROMdbo.syscolumns c
INNER JOIN dbo.systypes t ON c.xtype = t.xtype and c.usertype = t.usertype
WHEREc.id = OBJECT_ID(@sTableName))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[pr__SYS_MakeForEachTable] @bExecute as int
AS
--i know dynamic sql could do this in a single request but I can't get it to work.. sp_msforeachtable is not found for some reason even using Master..sp_msForEachTable
SET NOCOUNT ON
if @bExecute = 1
exec sp_MSForEachTable 'exec pr__SYS_MakeAllRecordProc ''?'', 1, 1'
else
exec sp_MSForEachTable 'exec pr__SYS_MakeAllRecordProc ''?'', 0, 1'
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC pr__SYS_MakeInsertRecordProc
@sTableName varchar(128),
@bExecute bit = 0
AS
SET NOCOUNT ON
IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
BEGIN
RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1)
RETURN
END
DECLARE@sProcText varchar(8000),
@sKeyFields varchar(2000),
@sAllFields varchar(2000),
@sAllParams varchar(2000),
@sWhereClause varchar(2000),
@sColumnName varchar(128),
@nColumnID smallint,
@bPrimaryKeyColumn bit,
@nAlternateType int,
@nColumnLength int,
@nColumnPrecision int,
@nColumnScale int,
@IsNullable bit,
@IsIdentity int,
@HasIdentity int,
@sTypeName varchar(128),
@sDefaultValue varchar(4000),
@sCRLF char(2),
@STAB char(1)
SET @HasIdentity = 0
SET@STAB = char(9)
SET @sCRLF = char(13) + char(10)
SET @sProcText = ''
SET @sKeyFields = ''
SET@sAllFields = ''
SET@sWhereClause = ''
SET@sAllParams = ''
SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''' + @sTableName + '_Insert'')' + @sCRLF
SET @sProcText = @sProcText + @STAB + 'DROP PROC ' + @sTableName + '_Insert' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET @sProcText = ''
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + '-- Insert a single record into ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + 'CREATE PROC [DBO].[' + @sTableName + '_Insert]' + @sCRLF
DECLARE crKeyFields cursor read_only for
SELECT sColumnName, nColumnID, bPrimaryKeyColumn, nAlternateType, nColumnLength, nColumnPrecision, nColumnScale, IsNullable, IsIdentity, sTypeName, sDefaultValue
FROMdbo.fnTableColumnInfo(@sTableName)
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IsIdentity = 0)
BEGIN
IF (@sKeyFields '')
SET @sKeyFields = @sKeyFields + ',' + @sCRLF
SET @sKeyFields = @sKeyFields + @STAB + '@' + @sColumnName + ' ' + @sTypeName
IF (@sAllFields '')
BEGIN
SET @sAllParams = @sAllParams + ', '
SET @sAllFields = @sAllFields + ', '
END
IF (@sTypeName = 'timestamp')
SET @sAllParams = @sAllParams + 'NULL'
ELSE IF (@sDefaultValue IS NOT NULL)
SET @sAllParams = @sAllParams + 'COALESCE(@' + @sColumnName + ', ' + @sDefaultValue + ')'
ELSE
SET @sAllParams = @sAllParams + '@' + @sColumnName
SET @sAllFields = @sAllFields + @sColumnName
END
ELSE
BEGIN
SET @HasIdentity = 1
END
IF (@nAlternateType = 2) --decimal, numeric
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', '
+ CAST(@nColumnScale AS varchar(3)) + ')'
ELSE IF (@nAlternateType = 1) --character and binary
BEGIN
IF @sTypeName = 'nvarchar'
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(4)) + ')'
ELSE
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) + ')'
END
IF (@IsIdentity = 0)
BEGIN
IF (@sDefaultValue IS NOT NULL) OR (@IsNullable = 1) OR (@sTypeName = 'timestamp')
SET @sKeyFields = @sKeyFields + ' = NULL'
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + 'AS' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + 'INSERT ' + @sTableName + '(' + @sAllFields + ')' + @sCRLF
SET @sProcText = @sProcText + 'VALUES (' + @sAllParams + ')' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
IF (@HasIdentity = 1)
BEGIN
SET @sProcText = @sProcText + 'RETURN SCOPE_IDENTITY()' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
END
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC pr__SYS_MakeSelectRecordProc
@sTableName varchar(128),
@bExecute bit = 0
AS
SET NOCOUNT ON
IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
BEGIN
RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1)
RETURN
END
DECLARE@sProcText varchar(8000),
@sKeyFields varchar(2000),
@sSelectClause varchar(2000),
@sWhereClause varchar(2000),
@sColumnName varchar(128),
@nColumnID smallint,
@bPrimaryKeyColumn bit,
@nAlternateType int,
@nColumnLength int,
@nColumnPrecision int,
@nColumnScale int,
@IsNullable bit,
@IsIdentity int,
@sTypeName varchar(128),
@sDefaultValue varchar(4000),
@sCRLF char(2),
@STAB char(1)
SET@STAB = char(9)
SET @sCRLF = char(13) + char(10)
SET @sProcText = ''
SET @sKeyFields = ''
SET@sSelectClause = ''
SET@sWhereClause = ''
SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''' + @sTableName + '_Select'')' + @sCRLF
SET @sProcText = @sProcText + @STAB + 'DROP PROC ' + @sTableName + '_Select' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET @sProcText = ''
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + '-- Select a single record from ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + 'CREATE PROC [DBO].[' + @sTableName + '_Select]' + @sCRLF
DECLARE crKeyFields cursor read_only for
SELECT sColumnName, nColumnID, bPrimaryKeyColumn, nAlternateType, nColumnLength, nColumnPrecision, nColumnScale, IsNullable, IsIdentity, sTypeName, sDefaultValue
FROMdbo.fnTableColumnInfo(@sTableName)
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@bPrimaryKeyColumn = 1)
BEGIN
IF (@sKeyFields '')
SET @sKeyFields = @sKeyFields + ',' + @sCRLF
SET @sKeyFields = @sKeyFields + @STAB + '@' + @sColumnName + ' ' + @sTypeName
IF (@nAlternateType = 2) --decimal, numeric
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', '
+ CAST(@nColumnScale AS varchar(3)) + ')'
ELSE IF (@nAlternateType = 1) --character and binary
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) + ')'
IF (@sWhereClause = '')
SET @sWhereClause = @sWhereClause + 'WHERE '
ELSE
SET @sWhereClause = @sWhereClause + ' AND '
SET @sWhereClause = @sWhereClause + @STAB + @sColumnName + ' = @' + @sColumnName + @sCRLF
END
IF (@sSelectClause = '')
SET @sSelectClause = @sSelectClause + 'SELECT'
ELSE
SET @sSelectClause = @sSelectClause + ',' + @sCRLF
SET @sSelectClause = @sSelectClause + @STAB + @sColumnName
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sSelectClause = @sSelectClause + @sCRLF
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + 'AS' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + @sSelectClause
SET @sProcText = @sProcText + 'FROM' + @sTableName + @sCRLF
SET @sProcText = @sProcText + @sWhereClause
SET @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC pr__SYS_MakeUpdateRecordProc
@sTableName varchar(128),
@bExecute bit = 0
AS
SET NOCOUNT ON
IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
BEGIN
RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1)
RETURN
END
DECLARE@sProcText varchar(8000),
@sKeyFields varchar(2000),
@sSetClause varchar(2000),
@sWhereClause varchar(2000),
@sColumnName varchar(128),
@nColumnID smallint,
@bPrimaryKeyColumn bit,
@nAlternateType int,
@nColumnLength int,
@nColumnPrecision int,
@nColumnScale int,
@IsNullable bit,
@IsIdentity int,
@sTypeName varchar(128),
@sDefaultValue varchar(4000),
@sCRLF char(2),
@STAB char(1)
SET@STAB = char(9)
SET @sCRLF = char(13) + char(10)
SET @sProcText = ''
SET @sKeyFields = ''
SET@sSetClause = ''
SET@sWhereClause = ''
SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''' + @sTableName + '_Update'')' + @sCRLF
SET @sProcText = @sProcText + @STAB + 'DROP PROC [DBO].[' + @sTableName + '_Update]' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET @sProcText = ''
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + '-- Update a single record in ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + 'CREATE PROC [DBO].[' + @sTableName + '_Update]' + @sCRLF
DECLARE crKeyFields cursor read_only for
SELECT sColumnName, nColumnID, bPrimaryKeyColumn, nAlternateType, nColumnLength, nColumnPrecision, nColumnScale, IsNullable, IsIdentity, sTypeName, sDefaultValue
FROMdbo.fnTableColumnInfo(@sTableName)
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@sKeyFields '')
SET @sKeyFields = @sKeyFields + ',' + @sCRLF
SET @sKeyFields = @sKeyFields + @STAB + '@' + @sColumnName + ' ' + @sTypeName
IF (@nAlternateType = 2) --decimal, numeric
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', '
+ CAST(@nColumnScale AS varchar(3)) + ')'
ELSE IF (@nAlternateType = 1) --character and binary
BEGIN
IF @sTypeName = 'nvarchar'
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(4)) + ')'
ELSE
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) + ')'
END
IF (@bPrimaryKeyColumn = 1)
BEGIN
IF (@sWhereClause = '')
SET @sWhereClause = @sWhereClause + 'WHERE '
ELSE
SET @sWhereClause = @sWhereClause + ' AND '
SET @sWhereClause = @sWhereClause + @STAB + @sColumnName + ' = @' + @sColumnName + @sCRLF
END
ELSE
IF (@IsIdentity = 0)
BEGIN
IF (@sSetClause = '')
SET @sSetClause = @sSetClause + 'SET'
ELSE
SET @sSetClause = @sSetClause + ',' + @sCRLF
SET @sSetClause = @sSetClause + @STAB + @sColumnName + ' = '
IF (@sTypeName = 'timestamp')
SET @sSetClause = @sSetClause + 'NULL'
ELSE IF (@sDefaultValue IS NOT NULL)
SET @sSetClause = @sSetClause + 'COALESCE(@' + @sColumnName + ', ' + @sDefaultValue + ')'
ELSE
SET @sSetClause = @sSetClause + '@' + @sColumnName
END
IF (@IsIdentity = 0)
BEGIN
IF (@IsNullable = 1) OR (@sTypeName = 'timestamp')
SET @sKeyFields = @sKeyFields + ' = NULL'
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sSetClause = @sSetClause + @sCRLF
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + 'AS' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + 'UPDATE' + @sTableName + @sCRLF
SET @sProcText = @sProcText + @sSetClause
SET @sProcText = @sProcText + @sWhereClause
SET @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC pr__SYS_MakeDeleteRecordProc
@sTableName varchar(128),
@bExecute bit = 0
AS
SET NOCOUNT ON
IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
BEGIN
RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1)
RETURN
END
DECLARE@sProcText varchar(8000),
@sKeyFields varchar(2000),
@sWhereClause varchar(2000),
@sColumnName varchar(128),
@nColumnID smallint,
@bPrimaryKeyColumn bit,
@nAlternateType int,
@nColumnLength int,
@nColumnPrecision int,
@nColumnScale int,
@IsNullable bit,
@IsIdentity int,
@sTypeName varchar(128),
@sDefaultValue varchar(4000),
@sCRLF char(2),
@STAB char(1)
SET@STAB = char(9)
SET @sCRLF = char(13) + char(10)
SET @sProcText = ''
SET @sKeyFields = ''
SET@sWhereClause = ''
SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''' + @sTableName + '_Delete'')' + @sCRLF
SET @sProcText = @sProcText + @STAB + 'DROP PROC ' + @sTableName + '_Delete' + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET @sProcText = ''
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + '-- Delete a single record from ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + 'CREATE PROC [DBO].[' + @sTableName + '_Delete]' + @sCRLF
DECLARE crKeyFields cursor read_only for
SELECT sColumnName, nColumnID, bPrimaryKeyColumn, nAlternateType, nColumnLength, nColumnPrecision, nColumnScale, IsNullable, IsIdentity, sTypeName, sDefaultValue
FROMdbo.fnTableColumnInfo(@sTableName)
ORDER BY 2
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@bPrimaryKeyColumn = 1)
BEGIN
IF (@sKeyFields '')
SET @sKeyFields = @sKeyFields + ',' + @sCRLF
SET @sKeyFields = @sKeyFields + @STAB + '@' + @sColumnName + ' ' + @sTypeName
IF (@nAlternateType = 2) --decimal, numeric
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', '
+ CAST(@nColumnScale AS varchar(3)) + ')'
ELSE IF (@nAlternateType = 1) --character and binary
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) + ')'
IF (@sWhereClause = '')
SET @sWhereClause = @sWhereClause + 'WHERE '
ELSE
SET @sWhereClause = @sWhereClause + ' AND '
SET @sWhereClause = @sWhereClause + @STAB + @sColumnName + ' = @' + @sColumnName + @sCRLF
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sProcText = @sProcText + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + 'AS' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + 'DELETE' + @sTableName + @sCRLF
SET @sProcText = @sProcText + @sWhereClause
SET @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[pr__SYS_MakeAllRecordProc] @sTableName as varchar(128), @bExecute as bit = 0, @UsingMsForEach as bit = 0
AS
SET NOCOUNT ON
if @UsingMsForEach = 1
BEGIN
SET @sTableName = substring(@sTableName, charindex('.', @sTableName) + 2, len(@sTableName) - charindex('.', @sTableName) - 2)
END
exec dbo.pr__SYS_MakeDeleteRecordProc @sTableName, @bExecute
exec dbo.pr__SYS_MakeInsertRecordProc @sTableName, @bExecute
exec dbo.pr__SYS_MakeSelectRecordProc @sTableName, @bExecute
exec dbo.pr__SYS_MakeUpdateRecordProc @sTableName, @bExecute
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- 'NullsPourcentage' is the table name to generate the code, 0 only generate the code for the sp, 1 CREATES the SP
exec dbo.pr__SYS_MakeAllRecordProc 'NullsPourcentage', 0
returns :
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'NullsPourcentage_Delete')
DROP PROC NullsPourcentage_Delete
GO
----------------------------------------------------------------------------
-- Delete a single record from NullsPourcentage
----------------------------------------------------------------------------
CREATE PROC [DBO].[NullsPourcentage_Delete]
@PkNullPourcentage int
AS
DELETENullsPourcentage
WHERE PkNullPourcentage = @PkNullPourcentage
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'NullsPourcentage_Insert')
DROP PROC NullsPourcentage_Insert
GO
----------------------------------------------------------------------------
-- Insert a single record into NullsPourcentage
----------------------------------------------------------------------------
CREATE PROC [DBO].[NullsPourcentage_Insert]
@DBName varchar(128),
@TableName varchar(258),
@ColName varchar(256),
@Colid smallint,
@TotalLines int,
@NullLines int,
@NonNullLines int,
@NullsPourcentage decimal(18, 3)
AS
INSERT NullsPourcentage(DBName, TableName, ColName, Colid, TotalLines, NullLines, NonNullLines, NullsPourcentage)
VALUES (@DBName, @TableName, @ColName, @Colid, @TotalLines, @NullLines, @NonNullLines, @NullsPourcentage)
RETURN SCOPE_IDENTITY()
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'NullsPourcentage_Select')
DROP PROC NullsPourcentage_Select
GO
----------------------------------------------------------------------------
-- Select a single record from NullsPourcentage
----------------------------------------------------------------------------
CREATE PROC [DBO].[NullsPourcentage_Select]
@PkNullPourcentage int
AS
SELECTPkNullPourcentage,
DBName,
TableName,
ColName,
Colid,
TotalLines,
NullLines,
NonNullLines,
NullsPourcentage
FROMNullsPourcentage
WHERE PkNullPourcentage = @PkNullPourcentage
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'NullsPourcentage_Update')
DROP PROC [DBO].[NullsPourcentage_Update]
GO
----------------------------------------------------------------------------
-- Update a single record in NullsPourcentage
----------------------------------------------------------------------------
CREATE PROC [DBO].[NullsPourcentage_Update]
@PkNullPourcentage int,
@DBName varchar(128),
@TableName varchar(258),
@ColName varchar(256),
@Colid smallint,
@TotalLines int,
@NullLines int,
@NonNullLines int,
@NullsPourcentage decimal(18, 3)
AS
UPDATENullsPourcentage
SETDBName = @DBName,
TableName = @TableName,
ColName = @ColName,
Colid = @Colid,
TotalLines = @TotalLines,
NullLines = @NullLines,
NonNullLines = @NonNullLines,
NullsPourcentage = @NullsPourcentage
WHERE PkNullPourcentage = @PkNullPourcentage
GO
pr__SYS_MakeForEachTable and pr__SYS_MakeAllRecordProc are of my creation... You can pretty much build 50% of the sps you need on the server in a single execution but they take quite some time to run if you have 100s of tables (few minutes... better to run that off hours).
March 1, 2005 at 10:07 am
WOW!!!
Thanks so much Remi, I'll try to build it tonight after hours and give it a try.
I really appreciate your help with this.
Craig
March 1, 2005 at 10:12 am
You can test this script on the northwind db and see how long it runs... shouldn't take more than a few secs.
Also you could modify the foreach proc to run only the inserts sp so it would run 3 times faster (delete query takes almost no time to run because it takes only 1 param to generate it)... or you could add a waitfor delay so it doesn't eat up the server alive.
July 30, 2007 at 5:13 pm
FYI - the original microsoft article
http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply