SQL Server Performance Forum – Threads Archive

Remove Identity field SQL 6.5

We have an interesting situation I could really do with some help on: Need to remove an identity column from a table in a SQL 6.5 database. Actually, I need to do this from about 10 tables in approximately 150 sites to allow for the use of our new product manager application that uses a SQL 2000 db and requires the ID’s in the SQL 2000 db to be present in all of the sites. (A centralised product manager). Now, this gets a little more complicated as not all of our sites are running the same version of software. As a result, some tables may have e.g. 10 columns and some may have 12. In SQL 6.5. I will have to: 1) Create the table with the desired definition. 2) Insert information across from the table to alter. 3) Drop constraints and indexes for later re-creation. 4) Script triggers. 5) Drop original table. 6) Rename new table to original table name. 7) Re-apply triggers to the table. <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Re-apply constraints and indexes. Should I use SQL OLE to script the table objects or is there a stored procedure around that can be used to script a table for creation, along with it’s triggers? or… am I able to use DTS ro SQL DMO from the SQL 2000 box to control this? I have written a set of stored procedures to deal with the dropping and subsequent re-creation of constraints and indexes on a desired table, mainly by plagiarising the code behind some of SQL servers sp_… stored procedures. I have untill Thursday to do this and was informed today at 3pm. Hope you can help. Thanx in advance. Rik

Apart from triggers have managed to create something to do the afore mentioned task.
I have attached it for your info. (Not fully tested yet, but works for a few tables). SET NOCOUNT ON
GO
create table #_parameters
(default_user varchar(10) not null,
table_prefix varchar(5) not null,
old_renamed varchar(5) not null) insert into #_parameters
values (‘dbo’, ‘_’, ‘old_’)
GO
PRINT ‘DROP TEMPORARY TABLES FOR BUILDING STATEMENTS TOT EXECUTE’
if exists (select * from sysobjects where id = object_id(‘dbo._cpm_TableDefinitions’) and sysstat & 0xf = 3)
drop table _cpm_TableDefinitions
GO
if exists (select * from sysobjects where id = object_id(‘dbo._cpm_TableCreation’) and sysstat & 0xf = 3)
drop table _cpm_TableCreation
GO
PRINT ‘CREATE TEMPORARY TABLES FOR BUILDING STATEMENTS TOT EXECUTE’
GO
CREATE TABLE _cpm_TableDefinitions
(tableid int identity(1,1) not null,
user_name varchar(10) null, — values applied later
table_name varchar(50) null, — values applied later
col_name varchar(50) not null,
col_id int not null,
DataType varchar(30) not null,
col_len int not null,
col_prec int null,
col_scale int null,
BaseType varchar(30) not null,
col_defname varchar(50) null,
col_rulname varchar(50) null,
col_null int not null,
col_identity int not null,
col_flags int not null,
col_seed int null,
col_increment int null,
col_dridefname varchar(30) null,
col_drideftext varchar(100) null
)
GO
CREATE TABLE _cpm_TableCreation
(table_id int identity(1,1) not null,
OLD_table_name varchar(50) not null,
NEW_table_name varchar(50) not null,
create_statement varchar(50) null
)
GO
PRINT ‘CORRECT ALL SYSTEM TABLE INDEXES IF OUT OF SYNC’
EXEC sp_MSforeachtable @command1="print ‘?’ DBCC DBREINDEX (‘?’)"
GO
PRINT ‘POPULATE TABLE WITH TABLE SCHEMA FOR DESIRED TABLES’
DECLARE @table_name VARCHAR(50)
DECLARE @user_name VARCHAR(10)
DECLARE @fq_table_name VARCHAR(50) SELECT @user_name = default_user from #_parameters — USER PERMISSIONS DECLARE Table_Cursor CURSOR FOR
(SELECT DISTINCT o.Name
FROM SysObjects o
JOIN SysColumns c ON (o.ID = c.ID)
WHERE o.Type = ‘u’
and o.Name = ‘desired_table’) — LIST ALL TABLES YOU WANT A SCHEMA CREATED FOR
ORDER BY o.Name OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
— ALL WB TABLES ARE DBO..
SELECT @fq_table_name = @user_name + ‘.’ + @table_name insert into _cpm_TableDefinitions
(col_name, col_id, DataType, col_len, col_prec, col_scale, BaseType, col_defname, col_rulname,
col_null, col_identity, col_flags, col_seed, col_increment, col_dridefname, col_drideftext)
exec sp_MShelpcolumns @fq_table_name, 512, @orderby = ‘id’ update _cpm_TableDefinitions
set user_name = @user_name,
table_name = @table_name
where tableid >= (select min(tableid) from _cpm_TableDefinitions (nolock) where table_name is null) FETCH NEXT FROM Table_Cursor INTO @table_name
END CLOSE Table_Cursor
DEALLOCATE Table_Cursor
GO
PRINT ‘REMOVE TABLES FROM _cpm_TableDefinitions WHERE TABLE DOES NOT HAVE IDENTITY COLUMN ON PRODUCTID COLUMN’ DELETE FROM _cpm_TableDefinitions
WHEREuser_name + ‘.’ + table_name NOT IN
(SELECT distinct user_name + ‘.’ + table_name
FROM _cpm_TableDefinitions
WHERE col_identity = 1
and col_name = ‘desired_column’)
GO
PRINT ‘POPULATE TABLE _cpm_TableCreation WITH CREATE TABLE STATEMENTS USING RETRIEVED TABLE SCHEMAS’
DECLARE @CREATE_STATEMENTvarchar(255)
DECLARE @INSERT_STATEMENT varchar(255)
DECLARE @prev_table_name varchar(50)
DECLARE @tableidint
DECLARE @table_name varchar(50)
DECLARE @col_name varchar(50)
DECLARE @col_id int
DECLARE @DataType varchar(30)
DECLARE @col_len int
DECLARE @col_prec int
DECLARE @col_scale int
DECLARE @BaseType varchar(30)
DECLARE @col_defname varchar(50)
DECLARE @col_rulname varchar(50)
DECLARE @col_null int
DECLARE @col_identity int
DECLARE @col_flags int
DECLARE @col_seed int
DECLARE @col_increment int
DECLARE @col_dridefname varchar(30)
DECLARE @col_drideftextvarchar(100)
DECLARE @user_name varchar(10)
DECLARE @fq_table_name varchar(50)
DECLARE @fq_orig_table_name varchar(50)
DECLARE @table_name_prefixvarchar(5) select @table_name_prefix = table_prefix
from #_parameters DECLARE CreateStatement_Cursor CURSOR FOR
(SELECT *
FROM _cpm_TableDefinitions)
ORDER BY table_name, col_id OPEN CreateStatement_Cursor FETCH NEXT FROM CreateStatement_Cursor INTO @tableid, @user_name, @table_name, @col_name, @col_id, @DataType, @col_len,
@col_prec, @col_scale, @BaseType, @col_defname, @col_rulname, @col_null,
@col_identity, @col_flags, @col_seed, @col_increment, @col_dridefname,
@col_drideftext
WHILE @@FETCH_STATUS = 0
BEGIN
select @fq_table_name = @user_name + ‘.’ + @table_name_prefix + @table_name,
@fq_orig_table_name = @user_name + ‘.’ + @table_name — SELECT * FROM _cpm_TableDefinitions WHERE DATATYPE <> BASETYPE — 0 Rows Returned
if (@prev_table_name <> @table_name OR @prev_table_name IS NULL)
begin
IF (NOT @CREATE_STATEMENT IS NULL) — DO NOT PERFORM ON FIRST CALL
BEGIN
update _cpm_TableCreation
set create_statement = substring(create_statement, 1, LEN(create_statement) – 3) + ‘)’
from_cpm_TableCreation
where table_id = (select max(table_id) from _cpm_TableCreation)
END select @prev_table_name = @table_name SELECT @CREATE_STATEMENT = ‘CREATE TABLE ‘ + @fq_table_name + ‘(‘
INSERT INTO _cpm_TableCreation (OLD_table_name, NEW_table_name, create_statement)
VALUES (@fq_orig_table_name, @fq_table_name, @CREATE_STATEMENT)
end
SELECT @CREATE_STATEMENT = @col_name + ‘ ‘ +
@DataType +
CASE @DataType WHEN ‘varchar’THEN ‘(‘ + convert(varchar(15), @col_len) + ‘)’
WHEN ‘char’THEN ‘(‘ + convert(varchar(15), @col_len) + ‘)’ END +
CASE @col_null WHEN 0 THEN ‘ NOT NULL,’ ELSE ‘ NULL,’ END
INSERT INTO _cpm_TableCreation (OLD_table_name, NEW_table_name, create_statement)
VALUES (@fq_orig_table_name, @fq_table_name, @CREATE_STATEMENT) — check number of fields — FETCH NEXT FROM CreateStatement_Cursor INTO @tableid, @user_name, @table_name, @col_name, @col_id, @DataType, @col_len,
@col_prec, @col_scale, @BaseType, @col_defname, @col_rulname, @col_null,
@col_identity, @col_flags, @col_seed, @col_increment, @col_dridefname,
@col_drideftext END update _cpm_TableCreation
set create_statement = substring(create_statement, 1, len(create_statement) – 1) + ‘)’
from_cpm_TableCreation
where table_id = (select max(table_id) from _cpm_TableCreation) CLOSE CreateStatement_Cursor
DEALLOCATE CreateStatement_Cursor GO
PRINT ‘CREATE A COPY OF THE DESIRED TABLES, WITH A TABLE NAME PREFIXED WITH AN "_"’
DECLARE @CREATE_STATEMENT1varchar(255)
DECLARE @CREATE_STATEMENT2varchar(255)
DECLARE @CREATE_STATEMENT3varchar(255)
DECLARE @CREATE_STATEMENT4varchar(255)
DECLARE @CREATE_STATEMENT5varchar(255)
DECLARE @create_statement varchar(50)
DECLARE @prev_table_name varchar(50)
DECLARE @old_table_name varchar(50)
DECLARE @new_table_namevarchar(50)
DECLARE @old_renamed varchar(50)
DECLARE @ERRORint
DECLARE @row_counterint select @old_renamed = old_renamed
from #_parameters IF EXISTS (SELECT OLD_table_name
FROM _cpm_TableCreation
GROUP BY OLD_table_name
HAVING count(OLD_table_name) > 25) — ONLY ENOUGH CREATE STATEMENTS TO HANDLE 25 ROWS
BEGIN
PRINT ‘MSG: TABLE DEFINITION IS GREATER THAN 25 LINES. THIS IS NOT CURRENTLY HANDLED’
GOTO ABORT
END DECLARE CreateStatement_Cursor CURSOR FOR
(SELECT OLD_table_name,
NEW_table_name,
create_statement
FROM _cpm_TableCreation)
ORDER BY table_id OPEN CreateStatement_Cursor FETCH NEXT FROM CreateStatement_Cursor INTO @old_table_name, @new_table_name, @create_statement
WHILE @@FETCH_STATUS = 0
BEGIN
if (@prev_table_name <> @new_table_name OR @prev_table_name IS NULL)
begin
IF (NOT @prev_table_name IS NULL)
BEGIN
— CREATE TABLE STATEMENT
exec (@CREATE_STATEMENT1 + @CREATE_STATEMENT2 + @CREATE_STATEMENT3 + @CREATE_STATEMENT4 + @CREATE_STATEMENT5)
if @@error > 0
begin
SELECT ‘MSG: CREATE STATEMENT FAILED TO EXECUTE FOR TABLE: ‘ + @new_table_name
end
else
begin
exec (‘insert into ‘ + @new_table_name + ‘ select * from ‘ + @old_table_name)
if @@error > 0
begin
SELECT ‘MSG: INSERT STATEMENT FAILED TO EXECUTE FOR TABLE: ‘ + @new_table_name
end
else
begin
exec @ERROR = xsp_integrity @old_table_name, 0
if @@error > 0
begin
SELECT ‘MSG: FAILED TO DROP INDEXES AND CONSTRAINTS ON TABLE: ‘ + @old_table_name
end
else
begin
select @old_renamed = @old_renamed + @old_table_name
exec sp_rename @old_table_name, @old_renamed, OBJECT
if @@error > 0
begin
SELECT ‘MSG: FAILED TO RENAME THE TABLE: ‘ + @old_table_name + ‘ TO: ‘ + @old_renamed
end
else
begin
exec sp_rename @new_table_name, @old_table_name, OBJECT
if @@error > 0
begin
SELECT ‘MSG: FAILED TO RENAME THE TABLE: ‘ + @new_table_name + ‘ TO: ‘ + @old_table_name
end
else
begin
exec @ERROR = xsp_integrity @old_table_name, 1
if @@error > 0
begin
SELECT ‘MSG: FAILED TO CREATE INDEXES AND CONSTRAINTS ON TABLE: ‘ + @new_table_name + ‘ TO: ‘ + @old_table_name
end
end
end
end
end
end
END select @prev_table_name = @new_table_name
select @CREATE_STATEMENT1 = ”
select @CREATE_STATEMENT2 = ”
select @CREATE_STATEMENT3 = ”
select @CREATE_STATEMENT4 = ”
select @CREATE_STATEMENT5 = ”
select @row_counter = 0
end
select @row_counter = @row_counter + 1
if (@row_counter <= 5)
select @CREATE_STATEMENT1 = @CREATE_STATEMENT1 + ltrim(rtrim(@create_statement))
if (@row_counter > 5 and @row_counter <= 10)
select @CREATE_STATEMENT2 = @CREATE_STATEMENT2 + ltrim(rtrim(@create_statement))
if (@row_counter > 10 and @row_counter <= 15)
select @CREATE_STATEMENT3 = @CREATE_STATEMENT3 + ltrim(rtrim(@create_statement))
if (@row_counter > 15 and @row_counter <= 20)
select @CREATE_STATEMENT4 = @CREATE_STATEMENT4 + ltrim(rtrim(@create_statement))
if (@row_counter > 20 and @row_counter <= 25)
select @CREATE_STATEMENT5 = @CREATE_STATEMENT5 + ltrim(rtrim(@create_statement)) FETCH NEXT FROM CreateStatement_Cursor INTO @old_table_name, @new_table_name, @create_statement
END CLOSE CreateStatement_Cursor
DEALLOCATE CreateStatement_Cursor IF (NOT @prev_table_name IS NULL)
BEGIN
— CREATE TABLE STATEMENT
exec (@CREATE_STATEMENT1 + @CREATE_STATEMENT2 + @CREATE_STATEMENT3 + @CREATE_STATEMENT4 + @CREATE_STATEMENT5)
if @@error > 0
begin
SELECT ‘MSG: CREATE STATEMENT FAILED TO EXECUTE FOR TABLE: ‘ + @new_table_name
end
else
begin
exec (‘insert into ‘ + @new_table_name + ‘ select * from ‘ + @old_table_name)
if @@error > 0
begin
SELECT ‘MSG: INSERT STATEMENT FAILED TO EXECUTE FOR TABLE: ‘ + @new_table_name
end
else
begin
exec @ERROR = xsp_integrity @old_table_name, 0
if @@error > 0
begin
SELECT ‘MSG: FAILED TO DROP INDEXES AND CONSTRAINTS ON TABLE: ‘ + @old_table_name
end
else
begin
select @old_renamed = @old_renamed + @old_table_name
exec sp_rename @old_table_name, @old_renamed, OBJECT
if @@error > 0
begin
SELECT ‘MSG: FAILED TO RENAME THE TABLE: ‘ + @old_table_name + ‘ TO: ‘ + @old_renamed
end
else
begin
exec sp_rename @new_table_name, @old_table_name, OBJECT
if @@error > 0
begin
SELECT ‘MSG: FAILED TO RENAME THE TABLE: ‘ + @new_table_name + ‘ TO: ‘ + @old_table_name
end
else
begin
exec @ERROR = xsp_integrity @old_table_name, 1
if @@error > 0
begin
SELECT ‘MSG: FAILED TO CREATE INDEXES AND CONSTRAINTS ON TABLE: ‘ + @new_table_name + ‘ TO: ‘ + @old_table_name
end
end
end
end
end
end
END ABORT:
GO
drop table #_parameters
GO
SET NOCOUNT OFF

]]>