Solved

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

Posted on 2008-06-18
11
280 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
  • 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
 

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 60

Accepted Solution

by:
chapmandew earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

896 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now