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

Changing a table will break a view?

The other day I updated a table to add a few columns using Enterprise
Manager. When I closed the table, it said it would update several other
database items as a result (very good, I thought).
However, subsequent to this change, even the most basic view is broken, e.g.
,
CREATE VIEW [v_table] AS select * from [table]
Recreating (or altering) the view clear up the problem. But, I was surprised
!
What is the canonical way to avoid views getting out of sync with tables? Is
there another way to modify table structure that will automatically recompil
e
other database objects?
David> What is the canonical way to avoid views getting out of sync with tables?
> Is
> there another way to modify table structure that will automatically
> recompile
> other database objects?
The best practice is to specify a column list rather than '*'. You can
execute sp_refreshview to refresh view meta data after changes to underlying
tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in message
news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
> The other day I updated a table to add a few columns using Enterprise
> Manager. When I closed the table, it said it would update several other
> database items as a result (very good, I thought).
> However, subsequent to this change, even the most basic view is broken,
> e.g.,
> CREATE VIEW [v_table] AS select * from [table]
> Recreating (or altering) the view clear up the problem. But, I was
> surprised!
> What is the canonical way to avoid views getting out of sync with tables?
> Is
> there another way to modify table structure that will automatically
> recompile
> other database objects?
> David
>|||Create your views WITH SCHEMABINDING to avoid this problem. That will
"bind" the schema -- meaning that none of the underlying tables will be able
to change unless you drop the view.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in message
news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
> The other day I updated a table to add a few columns using Enterprise
> Manager. When I closed the table, it said it would update several other
> database items as a result (very good, I thought).
> However, subsequent to this change, even the most basic view is broken,
e.g.,
> CREATE VIEW [v_table] AS select * from [table]
> Recreating (or altering) the view clear up the problem. But, I was
surprised!
> What is the canonical way to avoid views getting out of sync with tables?
Is
> there another way to modify table structure that will automatically
recompile
> other database objects?
> David
>|||And you can automate this refresh using something like:
-- Cycle through all view in the current database and refresh their metadata
-- to take into account any changes to the underlying objects.
DECLARE @.ViewName sysname
DECLARE views_to_refresh CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
OPEN views_to_refresh
FETCH NEXT FROM views_to_refresh INTO @.ViewName
WHILE @.@.FETCH_STATUS = 0
BEGIN
print 'Refreshing ' + @.ViewName
exec sp_refreshview @.ViewName
FETCH NEXT FROM views_to_refresh INTO @.ViewName
END
CLOSE views_to_refresh
DEALLOCATE views_to_refresh
Thanks!
David
"Dan Guzman" wrote:

> The best practice is to specify a column list rather than '*'. You can
> execute sp_refreshview to refresh view meta data after changes to underlyi
ng
> tables.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in messag
e
> news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
>
>sql

Change Parameter Entry View

Is there a way I can change the look of the field where you enter the parameters. I was wondering if it would be possible to change the display fonts and background colors. Thanks

No. The Parameter pane is not configurable.

Your request is not the first and is hopefully something that can be made available in the future.

|||

I think you can change some things, like the fonts, and even the size of the single select parameter box.

See: http://msdn2.microsoft.com/en-us/library/ms345247(SQL.90).aspx