July 5, 2008 at 4:48 am
Hi,
I'm using Vista and i have some issue that i need to know in order to do it efficiently in Ms SQL server 2008.
I have an excel/word file with all the tables name in a column along their attributes on the next column. There's around 300 tables and around 6000 row of attributes. Btw, i have all the real tables created already along the attributes(SMALL CAPS), each of these attribute's data type, allow null is set already. I just want to rename the attributes(SMALL CAPS) in the table from the source/excel file i have now to BIG CAPS attributes.( i have done already in the excel file).
Question: Is there any method that i can do this without RENAME/TYPING it 1 by 1 into the MSSQL?
Big Thanks.
Regards,
Darrenovic
July 5, 2008 at 6:42 am
I understand that the names of the database objects are in lower case and you want to have them in upper case?
Although I have posted a solution below, there is no reason for database objects be case sensitive and there are many drawbacks. If you need the data to be case sensitive, then specifiy the appropriate collation at the column level not at the server or database level
create table MyTestTable
(MyTestTable_name varchar(255)
COLLATE SQL_Latin1_General_Cp1_CS_AS
...
)
To change all table names from any case to upper case:
IF object_id('tempdb..#Rename') is not null drop table #Rename
select 'exec sp_rename @objname = '''
+ table_schema + '.' + table_name + ''''
+ ' , @newname = ''' + UPPER( table_name) + ''''
+ ' , @objtype = ''object''' as SQLStmt
into #Rename
frominformation_schema.tables
select * from #rename
--example row:
exec sp_rename @objname = 'Production.ProductPhoto' , @newname = 'PRODUCTPHOTO' , @objtype = 'object'
to execute the rename commands:
exec dbo.sp_execresultset @cmd = 'select SQLStmt from #Rename'
SQL = Scarcely Qualifies as a Language
July 5, 2008 at 10:46 am
Thank you for being helpful however i would like to apologized for not telling u my issue clearly.
Lets say the SMALL CAPS attributes is customer_id, i need to change it to CustomerId. But there's situation, i need to add in the tablename in front of the attributes. Let's say Table name is Customer. Then, the attributes would looks something like CustomerCustomerId. or maybe CustomerCustomerName. Please ignore why i would want to do that, because i must follow some sort of convention format of the project's database.
Therefore, the solution you recommended will not work nor anything (i believe) that using sql commands to auto edit it.
To tell you the whole picture, the source of these 300 tables are under a converted process from postgres to mssql. So in the middle, there could be some data naming isnt accurate and it scatter into all sort of type (eg. something like account_proformastatement, some are spaced some are underscored). It actually needs some sort of intelligence to edit each of these attributes into a standard format (AccountProFormaStatement). So that's the story.
The good thing is, I've already manually DONE all the editing and renaming done in excel/word. I just need to copy it and paste it into each of the attribute and it WILL WORK if i were gonna do that. But doing that into 6000 attributes takes too much time and effort and human error might occur.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply