Solved

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

Posted on 2008-06-18
11
279 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 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

18 Experts available now in Live!

Get 1:1 Help Now