SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Change all table name , column datatype
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shanmugaraj
Posting Yak Master

219 Posts

Posted - 03/14/2013 :  06:04:25  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/14/2013 :  06:19:35  Show Profile  Reply with Quote

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




--
Chandu
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 03/14/2013 :  06:47:20  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/14/2013 :  06:52:34  Show Profile  Reply with Quote
But sp_msforeachtable is undocumented procedure


--
Chandu
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 03/14/2013 :  06:57:58  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
I am doing it one time only for creating new OLAP Db from my OLTP

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/14/2013 :  07:01:51  Show Profile  Reply with Quote
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
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 03/14/2013 :  07:07:09  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
Ok thanks

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/14/2013 :  07:12:56  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000