Changing all Int to Bigint

Hi,
We need to change all Int data types to Bigint. Considering the number of
tables and View tables, changing manually one by one is not a option. Is
there a nicer way of changing all (hundreds of them) in more automatic way?
YCYou'll have to script this kind of thing. You can start with
INFORMATION_SCHEMA.COLUMNS and filter on DATA_TYPE. You can generate a
bunch of ALTER TABLE statements from that.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"YC" <asppsa@.hotmail.com> wrote in message
news:%23NDqO2DhGHA.4452@.TK2MSFTNGP05.phx.gbl...
Hi,
We need to change all Int data types to Bigint. Considering the number of
tables and View tables, changing manually one by one is not a option. Is
there a nicer way of changing all (hundreds of them) in more automatic way?
YC|||On Tue, 30 May 2006 16:05:30 -0700, YC wrote:

>Hi,
>We need to change all Int data types to Bigint. Considering the number of
>tables and View tables, changing manually one by one is not a option. Is
>there a nicer way of changing all (hundreds of them) in more automatic way?
Hi YC,
Tom told you how to do this. But I just have to ask why you want to do
this. I can imagine that the -2,147,483,648 to 2,147,483,647 range is
insufficient for SOME columns - but all'?
Have you considered the impact that this change will have on your DB's
storage requirements? All int columns will double in size - this will
impact storage requirement for tables with integer columns, but for
indexes on integer columns as well (and any nonclustered indexes if the
clustered index of the same table is on an integer column).
How about performance - more bytes per data row (and per index row)
means less rows per page. Means more logical page reads. But also less
cache hits, and hence more physical page reads.
I recommend you to change just the columns that need the ennhanced range
of bigint. Keep other columns as int.
Hugo Kornelis, SQL Server MVP|||I have to agree. One more thing - you'll have to drop any constraints or
indexes that use the columns before you can alter the columns. You'll then
have to replace them when you're done.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:p36s72ht36u3slffgfilhi2ic2lhp689tn@.
4ax.com...
On Tue, 30 May 2006 16:05:30 -0700, YC wrote:

>Hi,
>We need to change all Int data types to Bigint. Considering the number of
>tables and View tables, changing manually one by one is not a option. Is
>there a nicer way of changing all (hundreds of them) in more automatic way?
Hi YC,
Tom told you how to do this. But I just have to ask why you want to do
this. I can imagine that the -2,147,483,648 to 2,147,483,647 range is
insufficient for SOME columns - but all'?
Have you considered the impact that this change will have on your DB's
storage requirements? All int columns will double in size - this will
impact storage requirement for tables with integer columns, but for
indexes on integer columns as well (and any nonclustered indexes if the
clustered index of the same table is on an integer column).
How about performance - more bytes per data row (and per index row)
means less rows per page. Means more logical page reads. But also less
cache hits, and hence more physical page reads.
I recommend you to change just the columns that need the ennhanced range
of bigint. Keep other columns as int.
Hugo Kornelis, SQL Server MVPsql

Change ownership for User Defined Data Types

Hello,

Does anybody know an easy way to change User Defined Data Types ownership to dbo, without dropping dependent objects?

I know that there is a st. proc sp_changeobject owner, but it does not deal with this particular db objects. Is there a similar st.procedure or script that would do the same operation?

Thank youThere are no dependent object to user-defined datatypes. If you try scripting out a table that was created with user-defined datatypes, you'll find it scripts the original datatype.
Neither do objects "inherit" changes made to User-defined datatype.
Thus: User-defined datatypes are candidates for the most useless and misleading features of SQL Server, perhaps exceeded only by the Maintenance Plan Wizard.|||Well, may be I expressed myself wrong. I was trying to change ownership for UDDTs, and I the only way I knew was to drop them and recreate as "dbo". But these UDDTs were already used by many tables and stored procedures. So I would need to drop those as well and then restore the data. Too much hassle.
Anyway, I have already found the solution. This is the script:

sp_configure 'Allow Update',1
Reconfigure WITH OVERRIDE

Update sysTypes set uid = user_id('dbo') Where name = …

sp_configure 'Allow Update',0
Reconfigure WITH OVERRIDE

Change owner user defined datatype

Hello, We had a developer that created some user defined data types. He is no longer with our company and we want to change owner of those user defined data types so we will be able to delete his UUID. Is their a way to change the owner of the user defined data types from his ID to dbo. I don't see a way to change them with sp_changeobjectowner

Thanks in advance
Jef WainHere is the example, this code will change user defined datatype's owner from 'test' to 'dbo' which created by 'test' user

exec sp_configure 'allow updates', '1'

reconfigure WITH OVERRIDE

go

BEGIN TRANSACTION

update systypes set uid = user_id('dbo') where uid = user_id('test')

COMMIT TRANSACTION

exec sp_configure 'allow updates', '0'

reconfigure WITH OVERRIDE

go|||Thank you very much|||sp_changeobjectowner (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_1lpu.asp) will also do this, in a way that is supported by Microsoft.

-PatP

Change owner of user defined data types?

Hello is there a way to change the owner of a user defined data type in sql2000? If so help is appreciatedCheck this link,

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q327123

Change owner of User Defined Data Types

Please let me know if there is a method for changing the owner of existing User Defined Data Types. I cannot drop and recreate them as the datatypes are in use.
Thanks.Try, sp_changeobjectowner.
Refer to BOL for more information.|||sp_changeobjectowner cannot be used to change the owner of UDTs as it only works with tables, views, user-defined functions and stored procedures.|||Then only way is drop and recreate.|||As I stated in my original post, it is not possible to drop them as they are in use.|||Use specified SP to change owner for that table, which would be applied by default. Apart from this I have no other idea.

Change only USER tables to a new user

Hello,
I have a database created with several tables. There are two types
of table - system and user.
I want to change all the user tables ownership from dbo to another
user.
Is there a script that would allow me to do that - but only affect the
user type tables?
Thanks,
TmuldHi
"Tmuldoon" wrote:
> Hello,
> I have a database created with several tables. There are two types
> of table - system and user.
> I want to change all the user tables ownership from dbo to another
> user.
> Is there a script that would allow me to do that - but only affect the
> user type tables?
> Thanks,
> Tmuld
>
You don't give the version of SQL Server you are using! SQL 2005 has a ALTER
SCHEMA statement that will allow you to change the schema of a securable.
The tables to move could be identified by selecting them from the sys.tables
view.
If you are using SQL 2000, then I think you will need to re-create the table
and use INSERT..SELECT to move the data, you may have to drop the constraints
on the old table before creating the new table to avoid name conflicts. This
may be easier to do by scripting out the tables and editing the script.
Why do you need to change schemas?
John|||Tmuld,
If you are using SQL Server 2000 use: sp_changeobjectowner
If you are using SQL Server 2005 use: ALTER TABLE
You can create your own script by:
SELECT 'EXEC sp_changeobjectowner ''' +TABLE_SCHEMA+
'.'+TABLE_NAME + ''', ''NewOwner''', *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
The output of this SQL 2000 select is a script you can run. (Or edit first,
then run.)
If you use the ALTER TABLE approach then apply this idea to generating that
script. Be sure with SQL 2005 that you understand the difference between
'owner' and 'schema' since these are now separated.
RLF
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1176822600.477208.87050@.b75g2000hsg.googlegroups.com...
> Hello,
> I have a database created with several tables. There are two types
> of table - system and user.
> I want to change all the user tables ownership from dbo to another
> user.
> Is there a script that would allow me to do that - but only affect the
> user type tables?
> Thanks,
> Tmuld
>|||Hi
"Russell Fields" wrote:
> Tmuld,
> If you are using SQL Server 2000 use: sp_changeobjectowner
> If you are using SQL Server 2005 use: ALTER TABLE
> You can create your own script by:
> SELECT 'EXEC sp_changeobjectowner ''' +TABLE_SCHEMA+
> '.'+TABLE_NAME + ''', ''NewOwner''', *
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> ORDER BY TABLE_NAME
> The output of this SQL 2000 select is a script you can run. (Or edit first,
> then run.)
> If you use the ALTER TABLE approach then apply this idea to generating that
> script. Be sure with SQL 2005 that you understand the difference between
> 'owner' and 'schema' since these are now separated.
> RLF
> "Tmuldoon" <tmuldoon@.spliced.com> wrote in message
> news:1176822600.477208.87050@.b75g2000hsg.googlegroups.com...
> > Hello,
> >
> > I have a database created with several tables. There are two types
> > of table - system and user.
> >
> > I want to change all the user tables ownership from dbo to another
> > user.
> >
> > Is there a script that would allow me to do that - but only affect the
> > user type tables?
> >
> > Thanks,
> >
> > Tmuld
> >
I had forgotten about sp_changeobjectowner!
For SQL 2005 you could use
SELECT 'ALTER SCHEMA NewSchema TRANSFER [' +TABLE_SCHEMA + '].['+TABLE_NAME
+ ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
With both methods you will need to re-apply permissions to the tables.
John