Changing all existing tables collations in one step

I can change table collation first table column by column
then second table and so on (Alter table table_name
alter column column_name COLLATE < collation_name> ).
How can I change all existing tables collations in one step ?
Where can I find some program in Transact-SQL or some procedure to solve
this problem ?Am Tue, 11 Apr 2006 20:31:01 -0700 schrieb Alur:

> I can change table collation first table column by column
> then second table and so on (Alter table table_name
> alter column column_name COLLATE < collation_name> ).
> How can I change all existing tables collations in one step ?
> Where can I find some program in Transact-SQL or some procedure to solve
> this problem ?
You can change the collation only column by column, not in one step.
So you can read the info you need:
--
select so.name as TableName, sc.name as ColName, sc.collation from
syscolumns sc left join sysobjects so on so.id = sc.id
where IsNull(sc.name,'') > '' and so.xtype = 'U'
and sc.collation is not NULL
--
create a cursor, loop through this query and do the ALTER TABLE where the
collation is not like you need.
bye, Helmut|||Thank you.
"helmut woess" wrote:

> Am Tue, 11 Apr 2006 20:31:01 -0700 schrieb Alur:
>
> You can change the collation only column by column, not in one step.
> So you can read the info you need:
> --
> select so.name as TableName, sc.name as ColName, sc.collation from
> syscolumns sc left join sysobjects so on so.id = sc.id
> where IsNull(sc.name,'') > '' and so.xtype = 'U'
> and sc.collation is not NULL
> --
> create a cursor, loop through this query and do the ALTER TABLE where the
> collation is not like you need.
> bye, Helmut
>

Change the Column_Name Data Type with a query? Possible?

Hi everyone,
Ive got a small question: Is it possible to change the Column_Name Data Type from a SQL table with a query?
I want to do this with a query.
The Data Type ntext must be changed to text
Thanks in advance!i"m not shure what you want

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_table1
(
kolnew text,
)
GO
IF EXISTS(SELECT * FROM dbo.table1)
EXEC('INSERT INTO dbo.Tmp_table1 (kol)
SELECT CONVERT(text, kolnew) FROM dbo.table1 TABLOCKX')

GO
DROP TABLE dbo.tabla1
GO
EXECUTE sp_rename N'dbo.Tmp_table1', N'table1', 'OBJECT'
commit transaction|||Here you have a screenshot from what I mean: (Edited out - not usefull anymore!)|||so you have the answer|||THX! I'm gonna try it... :D

I hope it works!!

I asked this because off topic 'Euro symbol appears like a '?' instead ''