?
Solved

How to find and change data type of a specific field in all tables in a database?

Posted on 2008-06-18
11
Medium Priority
?
291 Views
Last Modified: 2010-04-21
I would like to change the data type of a specific field in my database.  This field is named "TRANS_TYPE_ID".  In most cases, this is a foreign key to the "TRANS_TYPE" table.  The issue is some of the fields have different datatypes [either numeric(10,0) or deciamal(10,0)].  It is a mystery on how this happened but there are relationships between the fields even though the datatypes are different.  The previous database format must of allowed this and was able to import it into SQL 2005 without any errors.  I don't know what has happened before me, but I am now asked to change all the "TRANS_TYPE_ID" fields to numeric(10).

Can anyone provide me with a script to query the database tables that contain the "TRANS_TYPE_ID" field name with database other than numeric(10,0) ?  Also, is there a way to loop through these tables and change the datatype with T-SQL?
0
Comment
Question by:sndip
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21815469
for all columns with the name trans_type_id and are numeric(10,0)

select * from information_schema.columns
where data_type = 'numeric' and numeric_precision = 10 and numeric_scale = 10
and column_name = 'TRANS_TYPE_ID'
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21815480
whoops....slight change to that...you wanted where it is not that data type


select * from information_schema.columns
where data_type != 'numeric' and numeric_precision = 10 and numeric_scale = 10
and column_name = 'TRANS_TYPE_ID'
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21815504
this will change them to numeric for you:


declare @TableName nvarchar(255), @ColumnName nvarchar(255), @sql nvarchar(1000)
 
DECLARE  CursorTemplate CURSOR 
FAST_FORWARD FOR 	
 
select table_name, column_name from information_schema.columns
where data_type != 'numeric' and numeric_precision = 10 and numeric_scale = 10
and column_name = 'TRANS_TYPE_ID'
 
OPEN CursorTemplate
 
FETCH NEXT FROM CursorTemplate 
INTO	@TableName, @ColumnName
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
	set @sql = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' NUMERIC(10,0)'
	execute sp_executesql @sql
	FETCH NEXT FROM CursorTemplate 
	INTO	@TableName, @ColumnName
 
 
END
 
CLOSE CursorTemplate
DEALLOCATE CursorTemplate

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sndip
ID: 21816028
Thanks for the quick response chapmandew!  I am actually trying to change all decimal(x, y) to numeric(x, y).  Since there are relationships on these columns, I am getting errors.  Is there a quick way to ignore contraints and then add them back?

So I've changed the script as follows:

declare @TableName nvarchar(255), @ColumnName nvarchar(255), @sql nvarchar(1000)
declare @Precision int, @Scale int, @DataType nvarchar(255)
 
DECLARE  CursorTemplate CURSOR
FAST_FORWARD FOR       
 
select table_name, column_name, numeric_precision, numeric_scale, data_type from information_schema.columns
where data_type = 'decimal'
 

OPEN CursorTemplate
 
FETCH NEXT FROM CursorTemplate
INTO      @TableName, @ColumnName, @Precision, @Scale, @DataType
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
      set @sql = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' NUMERIC(' + convert(varchar(50), @Precision) + ',' + convert(varchar(50), @Scale) + ')'

      print @sql
      execute sp_executesql @sql
      FETCH NEXT FROM CursorTemplate
      INTO      @TableName, @ColumnName, @Precision, @Scale, @DataType
END
 
CLOSE CursorTemplate
DEALLOCATE CursorTemplate

0
 

Author Comment

by:sndip
ID: 21816058
I tried this:
EXEC sp_msforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT all"

But no luck.  Here is the actual error I get:
Msg 5074, Level 16, State 1, Line 1
The object 'PK_TRANS_TYPE' is dependent on column 'trans_type_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'FK_TRANS_TRANS_TYPE' is dependent on column 'trans_type_id'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN trans_type_id failed because one or more objects access this column.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 1500 total points
ID: 21816061
Yes, but it involves creating new objects.....this will generate the statements for you.  MAKE sure you copy the contents out onto XL or something so you can run them later.
use master
go
CREATE PROCEDURE [dbo].[sp_ForeignKeyObjects]
AS
	SELECT 
		OBJECT_NAME(constid) AS ConstraintName,
		OBJECT_NAME(fkeyid) + '.' + COL_NAME(fkeyid, fkey) AS ForeignKeyObject,
		OBJECT_NAME(rkeyid) + '.' + COL_NAME(rkeyid, rkey) AS ReferenceKeyObject, 
		COL_NAME(fkeyid, fkey) AS ForeignKeyColumn,
		COL_NAME(rkeyid, rkey) AS ReferenceKeyColumn, 
		constid AS ConstraintID, 
		OBJECT_NAME(fkeyid) AS ForeignKeyTable, 		
		fkeyid AS ForeignKeyID, 
		OBJECT_NAME(rkeyid) AS ReferenceKeyTable,
		rkeyid AS ReferenceKeyID,
		keyno AS KeySequenceNumber
	FROM 
		sysforeignkeys
	ORDER BY 
		OBJECT_NAME(rkeyid) ASC, COL_NAME(rkeyid, rkey)
go
exec sp_ms_marksystemobject 'sp_ForeignKeyObjects'
go
use yourdatabasename
go
IF OBJECT_ID('tempdb..#FK')>0
	DROP TABLE #FK
 
IF OBJECT_ID('tempdb..#Const')>0
	DROP TABLE #Const
 
CREATE TABLE #FK
(
	ConstraintName VARCHAR(255),
	ForeignKeyObject VARCHAR(255),
	ReferenceObject VARCHAR(255),
	ForeignKeyColumn VARCHAR(255),
	ReferenceKeyColumn VARCHAR(255),
	ConstraintID INT, 
	ForeignKeyTable VARCHAR(255),
	ForeignKeyID INT, 
	ReferenceKeyTable VARCHAR(255),
	ReferenceKeyID INT, 
	KeySequenceNumber SMALLINT
)
 
--master..sp_helptext 'sp_foreignkeyobjects'
CREATE TABLE #Const
(
	ConstraintID INT, 
	FBuildField VARCHAR(2000) DEFAULT(''), 
	RBuildField VARCHAR(2000) DEFAULT(''), 
	CountField SMALLINT 
)
INSERT INTO #FK
EXEC sp_foreignkeyobjects
 
 
-- 
-- select * from #fk
SET NOCOUNT ON
DECLARE  tempcursor
CURSOR
READ_ONLY
FOR 
 
	select 	
		f.ConstraintName ,
		f.ForeignKeyObject ,
		f.ReferenceObject ,
		f.ForeignKeyColumn ,
		f.ReferenceKeyColumn,
		f.ConstraintID , 
		f.ForeignKeyID , 
		f.ReferenceKeyID , 
		f.KeySequenceNumber, 
		f.ForeignKeyTable,
		f.ReferenceKeyTable 
	from #FK AS  f
		INNER JOIN 
		(
			SELECT ConstraintID, MAX(KeySequenceNumber) AS MaxSeq
			FROM #FK AS  k
			--WHERE f.ConstraintID = k.ConstraintID AND f.KeySequenceNumber = k.MaxSeq
			GROUP BY k.ConstraintID
		)b ON f.ConstraintID = b.ConstraintID AND f.KeySequenceNumber = b.MaxSeq
		
--select * from #fk where constraintid = 738361945
 
 
DECLARE 
	@ConstraintName VARCHAR(255),
	@ForeignKeyObject VARCHAR(255),
	@ReferenceObject VARCHAR(255),
	@ForeignKeyColumn VARCHAR(255),
	@ReferenceKeyColumn VARCHAR(255),
	@ConstraintID INT, 
	@ForeignKeyID INT, 
	@ReferenceKeyID INT, 
	@KeySequenceNumber SMALLINT, 
	@ForeignKeyTable VARCHAR(255),
	@ReferenceKeyTable VARCHAR(255)
 
OPEN tempcursor
 
	FETCH NEXT FROM tempCursor INTO 
	@ConstraintName ,
	@ForeignKeyObject ,
	@ReferenceObject ,
	@ForeignKeyColumn ,
	@ReferenceKeyColumn,
	@ConstraintID , 
	@ForeignKeyID , 
	@ReferenceKeyID, 
	@KeySequenceNumber,
	@ForeignKeyTable ,
	@ReferenceKeyTable 
 
WHILE (@@fetch_status <> -1)
BEGIN
 
	DECLARE  tempcursor2
	CURSOR
	READ_ONLY
	FOR 
	
		SELECT ConstraintID, ForeignKeyColumn, ReferenceKeyColumn, KeySequenceNumber 
		FROM #FK 
		WHERE ConstraintID = @ConstraintID
 
		ORDER BY ConstraintID, KeySequenceNumber ASC
	
	DECLARE @ConstraintID2 INT, @ForeignKeyColumn2 VARCHAR(255), @ReferenceKeyColumn2 VARCHAR(255), @KeySequenceNumber2 SMALLINT
	DECLARE @FKeyBuildField VARCHAR(1000), @RKeyBuildField VARCHAR(1000), @Cnt SMALLINT 
	
	OPEN tempcursor2
	PRINT '--------------------------------------------------------'
	SELECT @FKeyBuildField = '', @RKeyBuildField = '', @Cnt = 0
 
	FETCH NEXT FROM tempcursor2 INTO @ConstraintID2 , @ForeignKeyColumn2 , @ReferenceKeyColumn2 , @KeySequenceNumber2
	WHILE (@@fetch_status <> -1)
	BEGIN
		SET @Cnt = @Cnt + 1
		SELECT @FKeyBuildField = @FKeyBuildField  + ISNULL(@ForeignKeyColumn2,'')+ 
			CASE 
				WHEN @ForeignKeyColumn2 IS NULL THEN '' 
			ELSE  
				CASE WHEN @KeySequenceNumber = @KeySequenceNumber2 THEN '' ELSE ',' END
			END
 
		SELECT @RKeyBuildField = @RKeyBuildField  + ISNULL(@ReferenceKeyColumn2,'')+ 
			CASE 
				WHEN @ReferenceKeyColumn2 IS NULL THEN '' 
			ELSE  
				CASE WHEN @KeySequenceNumber = @KeySequenceNumber2 THEN '' ELSE ',' END
			END
	
		INSERT INTO #Const
		(	
			ConstraintID , 
			FBuildField , 
			RBuildField, 
			CountField  
		)
		VALUES
		(
			@ConstraintID, 
			@FKeyBuildField,
			@RKeyBuildField,
			@Cnt
		)
 
		FETCH NEXT FROM tempcursor2 INTO @ConstraintID2 , @ForeignKeyColumn2 , @ReferenceKeyColumn2 , @KeySequenceNumber2
	END
	PRINT @FKeyBuildField + ' | ' + CAST(@KeySequenceNumber AS VARCHAR(100))+ ' | ' + CAST(@KeySequenceNumber2 AS VARCHAR(100))
	CLOSE tempcursor2
	DEALLOCATE tempcursor2
 
	FETCH NEXT FROM tempCursor INTO 
	@ConstraintName ,
	@ForeignKeyObject ,
	@ReferenceObject ,
	@ForeignKeyColumn ,
	@ReferenceKeyColumn,
	@ConstraintID , 
	@ForeignKeyID , 
	@ReferenceKeyID , 
	@KeySequenceNumber ,
	@ForeignKeyTable ,
	@ReferenceKeyTable 
END
 
CLOSE tempcursor
DEALLOCATE tempcursor
 
select 'ALTER TABLE [' + FKTable + '] DROP CONSTRAINT ' + OBJECT_NAME(a.ConstraintID) AS DropKeys,
'ALTER TABLE [' + FKTable + '] WITH NOCHECK ADD CONSTRAINT ' + OBJECT_NAME(a.ConstraintID) + ' FOREIGN KEY(' + FBuildField + ') REFERENCES ' + RKTable + '(' + RBuildField+')' AS BuildKeys
,*
from #Const a
join
(
	select ConstraintID, max(countfield) as maxcount
	from #Const
	group by ConstraintID
) b on a.ConstraintID = b.ConstraintID  and a.countfield = b.maxcount
join
(
select distinct constraintid, object_name(foreignkeyid) as FKTable, object_name(referencekeyid) AS RKTable from #fk
) c on a.constraintid = c.constraintid
 
DROP TABLE #Const

Open in new window

0
 

Author Comment

by:sndip
ID: 21816258
Even with the scripts that were generated to drop the relationships, I still get errors.  Attached are the creation of two related tables with decimal(10,0) fields.  I've generated the drop/build keys using your script and added them in the body but I still errors.
USE [Test]
GO
CREATE TABLE [dbo].[TRANS_TYPE](
	[trans_type_id] [decimal](10, 2) NOT NULL,
	[trans_type_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_TRANS_TYPE] PRIMARY KEY CLUSTERED 
(
	[trans_type_id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
CREATE TABLE [dbo].[TRANS](
	[trans_id] [int] NOT NULL,
	[trans_type_id] [decimal](10, 2) NULL,
	[trans_date] [datetime] NOT NULL,
 CONSTRAINT [PK_TRANS] PRIMARY KEY CLUSTERED 
(
	[trans_id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]
 
 
 
 
-- drop keys start --
ALTER TABLE [TRANS] DROP CONSTRAINT FK_TRANS_TRANS_TYPE
-- drop keys end --
 
 
 
declare @TableName nvarchar(255), @ColumnName nvarchar(255), @sql nvarchar(1000)
declare @Precision int, @Scale int, @DataType nvarchar(255)
 
 
EXEC sp_msforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_msforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER  all"
 
 
DECLARE  CursorTemplate CURSOR 
FAST_FORWARD FOR 	
 
select table_name, column_name, numeric_precision, numeric_scale, data_type from information_schema.columns
where data_type = 'decimal'
 
 
OPEN CursorTemplate
 
FETCH NEXT FROM CursorTemplate 
INTO	@TableName, @ColumnName, @Precision, @Scale, @DataType
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
	set @sql = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' NUMERIC(' + convert(varchar(50), @Precision) + ',' + convert(varchar(50), @Scale) + ')'
 
	print @sql
	execute sp_executesql @sql
	FETCH NEXT FROM CursorTemplate 
	INTO	@TableName, @ColumnName, @Precision, @Scale, @DataType
END
 
CLOSE CursorTemplate
DEALLOCATE CursorTemplate
 
 
EXEC sp_msforeachtable 	@command1="print '?'", @command2="ALTER TABLE ? CHECK CONSTRAINT all"
EXEC sp_msforeachtable 	@command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER  all"
 
 
 
-- buuild keys start --
ALTER TABLE [TRANS] WITH NOCHECK ADD CONSTRAINT FK_TRANS_TRANS_TYPE FOREIGN KEY(trans_type_id) REFERENCES TRANS_TYPE(trans_type_id)
-- buuild keys end --

Open in new window

0
 

Author Comment

by:sndip
ID: 21816446
Here is the sample table scripts:  (I didn't have the keys/contraints included before)
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TRANS_TYPE](
	[trans_type_id] [decimal](10, 2) NOT NULL,
	[trans_type_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_TRANS_TYPE] PRIMARY KEY CLUSTERED 
(
	[trans_type_id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TRANS]    Script Date: 06/18/2008 12:50:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TRANS](
	[trans_id] [int] NOT NULL,
	[trans_type_id] [decimal](10, 2) NULL,
	[trans_date] [datetime] NOT NULL,
 CONSTRAINT [PK_TRANS] PRIMARY KEY CLUSTERED 
(
	[trans_id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[TRANS_TYPE]  WITH CHECK ADD  CONSTRAINT [FK_TRANS_TYPE_TRANS_TYPE] FOREIGN KEY([trans_type_id])
REFERENCES [dbo].[TRANS_TYPE] ([trans_type_id])
GO
ALTER TABLE [dbo].[TRANS_TYPE] CHECK CONSTRAINT [FK_TRANS_TYPE_TRANS_TYPE]
GO
ALTER TABLE [dbo].[TRANS]  WITH CHECK ADD  CONSTRAINT [FK_TRANS_TRANS_TYPE] FOREIGN KEY([trans_type_id])
REFERENCES [dbo].[TRANS_TYPE] ([trans_type_id])
GO
ALTER TABLE [dbo].[TRANS] CHECK CONSTRAINT [FK_TRANS_TRANS_TYPE]

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21816460
why don't you just go through and drop the constraints.  Then, alter your tables, then use what you've scripted to recreate them?
0
 

Author Comment

by:sndip
ID: 21816505
I did, but that didn't work.  I think it is because of the PRIMARY KEYS are not included in the script to be dropped and re-build.  Could these be included in the script generator you provided?
0
 

Author Closing Comment

by:sndip
ID: 31468505
Good starting points but wasn't a complete solution.
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question