Name of Tables and Columns

Hi,
i just migrated an database from oracle to sql server 2005 with the migration tool from microsoft (v3). the migration tool works only with uppercase table and column names, but i need them in lower case. is there a way to modify the names of tables and columns with t-sql to lower case?
Thx
Frank
There is no "alter table ... rename " command.
I have done in following way :
1.In Management Studio select all the tables->right click->Script table as->create to->New query editor window
(all this after I eliminated some scripting setting in Tool->Options->Scripting)
2.Copy all the script
3. Paste in Word ->Format->Change case->lower case : and all the words are lower case-> select all and copy
4.Back in theStudio->paste in a new query window-> select a test db
5. Run that script
(6.Then Import Export wizard etc )|||

yes u can do this using sp_rename system stored procedure . read more in BOL

Madhu

|||

As Madhu indicated, use sp_rename.

EXECUTE sp_rename 'tablename', 'TableName', object

|||Thank you all for help, 'sp_rename' works fine.

MySQL to SQL Server migration

Hi,
I would liked to know the SQL Server equivalent for the below MySQL query :
SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1
Thanks,
SmithaYou can do it the same way except you can't just show a.col2. You use groupby, so it must belong to some logical output. You can do it like:

SELECT
a.col1,
max(a.col2), --or other functions...
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1

OR

SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1, a.col2

Hope it helps|||Adding all the select columns to the group by can't be the solution as the result will differ when compared to the MySQL query. Am I right ? Please advice.
Also I can't remove the select list item and replace it with aggregate function, as I require that too in my result set.|||imho, MSSQL is here logically correct. Which raw should sql give out if you use group by clause?

id | name
1 a
2 b
2 c
2 d
3 e
If you execute SELECT name, max(id) GROUP BY id, what should come out?
1 a
3 e
2 (?) <-- b, c or d?

So you have to be specific.|||MySQL always gives the first row '2 b'. I need the equivalent for this logic.|||Yes, and that's not definitely. MySQL gives out just the first row, and that's the first row saved in the table. The next time (say, after import-export) it may be an another row.|||SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1, a.col2
Your above solution gives all the rows, is there any equivalent SQL Server query which fetches the first row from the table
|||SELECT DISTINCT ?|||SELECT distinct MAX(id), name FROM test3 GROUP BY id, name
Above query is not helping me ! It gives all the rows|||SELECT MAX(id), min(name) FROM test3 GROUP BY id

here you'll get the right grouping. If you take min(name) you'll get the first (alphabetically) grouped name row|||Can you show some sample data and the output for that query in MySQL? I'm not sure how it should behave, given that Col1 is both grouped and in an aggregate function, and Col2 is not even grouped... Very odd... If you can show the input you're using and the output you need, I can help you write a query for it. Also, please note whether you're using SQL Server 2000 or 2005. -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <smith_kbase@.discussions.microsoft.com> wrote in message news:edc1b9b6-7367-4041-8e24-a17af5660785_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com Hi,I would liked to know the SQL Server equivalent for the below MySQL query :SELECTa.col1,a.col2,max(a.col1)FROM test a,test1 b WHERE a.col2 = b.col2GROUP BY a.col1Thanks,Smitha

MySQL to SQL Server - query migration

Hi,
I would liked to know the SQL Server equivalent for the below MySQL query :
SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1
Thanks,
MiraJSELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1,a.col2