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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2242 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
2242 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
2242 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
2242 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  
 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.06 seconds. Powered By: Snitz Forums 2000