/ *
Author: baikaishui
Time: 2005-4-23
Delete a column of data, the data of the latter column is automatically advanced, and the last column is automatically added to NULL.
* /
- Establish test data
Create Table ABC (ID INT, X1 INT, X2 INT, X3 INT, X4 INT, X5 INT, X6 INT, X7 INT, X8 INT)
INSERT ABC
SELECT 1, 1, 2, 3, 4, 5, 6, 7, 8
Go
Create Procedure Del_col
@column nvarchar (100),
@TableName Nvarchar (100)
AS
Declare @Colid Int
Declare @colmax int
Declare @colum nvarchar (100)
SELECT @ colid = syscolumns.colid from syscolumns join sysobjects on syscolumns.id = sysobjects.id
WHERE SYSOBJECTS.TYPE = 'u'and sysobjects.status> 0 and sysobjects.name=@tablename and syscolumns.name=@column
--Print @colid
Select @ colmax = max (syscolumns.colid) from syscolumns join sysobjects on syscolumns.id = sysObjects.ID
WHERE SYSOBJECTS.TYPE = 'urin sysobjects.status> 0 and sysobjects.name=@tablename
--Print @colmax
Declare @i int
Declare @ colum1 nvarchar (100)
Declare @ colum2 nvarchar (100)
Declare @sql nvarchar (500)
Set @ i = @ colid
While (@i <= @ colmax)
Begin
SET @ SQL = ''
IF (@i <@colmax)
Begin
SELECT @ colum1 = syscolumns.name from syscolumns join sysobjects on syscolumns.id = sysobjects.id
WHERE SYSOBJECTS.TYPE = 'u'and sysobjects.status> 0 and sysobjects.name=@tablename and syscolumns.colid=@i
SELECT @ colum2 = syscolumns.name from syscolumns join sysobjects on syscolumns.id = sysobjects.id
WHERE SYSOBJECTS.TYPE = 'u'and sysobjects.status> 0 and sysobjects.name=@tablename and syscolumns.colid=@i
Print @ colum1
Print @ colum2
Set @ SQL = N'Update ' @ TableName ' Set ' @ Colum1 ' = ' @ Colum2Print @SQL
EXEC SP_EXECUTESQL @SQL
end
Else
Begin
SELECT @ colum1 = syscolumns.name from syscolumns join sysobjects on syscolumns.id = sysobjects.id
WHERE SYSOBJECTS.TYPE = 'u'and sysobjects.status> 0 and sysobjects.name=@tablename and syscolumns.colid=@colmax
Print @ colum1
Set @ SQL = N'Update ' @ TableName ' Set ' @ Colum1 ' = NULL '
Print @SQL
EXEC SP_EXECUTESQL @SQL
end
Set @ i = @ i 1
end
Go
EXEC DEL_COL 'X3', 'ABC'
Select * from ABC
- Delete test data
Drop Procedure del_col
Drop Table ABC
