Change all table name , column datatype

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Change all table name , column datatype

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-03-14 : 06:04:25

1. add dim_ to all the tables in my database.
eg:
client
dim_client

2. alter column name ending with _id to _skey for all the columns in the entire database.
Eg:
country_id
country_skey

3. alter column datatype of numeric(18, 0) to numeric(9, 6) for all the columns in the entire database.
Eg:
salary numeric(18, 0) to numeric(9, 6)

I understand i have to write query FROM INFORMATION_SCHEMA table.

Kindly help me with the query

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 06:19:35

[code]
1) Write dynamic query for rename table name
For each table use sp_rename procedure
--change Table name
EXEC sp_RENAME '[OldTableName]' , '[NewTableName]'
2) Get column names which are ending with _skey, then
--change Column name
EXEC sp_RENAME 'TableName.[OldColumnName]', '[NewColumnName]', 'COLUMN'

3) ALTER TABLE command for changing column data type
[/code]

--
Chandu

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-03-14 : 06:47:20

I want this to happen for all the tables inspite of me providing the names of each table.
I am trying using sp_msforeachtable

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 06:52:34

But sp_msforeachtable is undocumented procedure

--
Chandu

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-03-14 : 06:57:58

I am doing it one time only for creating new OLAP Db from my OLTP

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 07:01:51

You can use these queries for getting list of requires table names and column name


SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES -- Table Names
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE		-- Column name which have _skey as ending tag
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%@_skey' ESCAPE '@'

SELECT TABLE_NAME, COLUMN_NAME   -- Data type dec(18,0) columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'decimal'
	AND NUMERIC_PRECISION = 18 
	AND NUMERIC_SCALE = 0

--
Chandu

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-03-14 : 07:07:09

Ok thanks

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 07:12:56

quote:
Originally posted by shanmugaraj

Ok thanks
THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Post your solution once you have done.. It might be useful to others in the forum...

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources