Upgrade Image&ntext to varbinarymax&nvarchar(max)
CREATE PROCEDURE SP_EXEC_WITH_LOG(@I_TICKETNO VARCHAR(10),@I_SQLSTR nvarchar(max))
AS
BEGIN
DECLARE @QUOTATION_TRANSFER_STR nvarchar(max);
SET @QUOTATION_TRANSFER_STR = REPLACE(@I_SQLSTR,'''','''''');
BEGIN TRY
EXEC SP_EXECUTESQL @I_SQLSTR;
EXEC GPUPGRADE_LOGGING @I_TICKETNO,@QUOTATION_TRANSFER_STR,'SUCCESS',0
END TRY
BEGIN CATCH
declare @message varchar(2000);
select @message = ERROR_MESSAGE();
EXEC GPUPGRADE_LOGGING @I_TICKETNO,@QUOTATION_TRANSFER_STR,@message,1
END CATCH
END
GO
CREATE PROCEDURE UPDATE_IMAGE_TO_VARBINARYMAX
AS
BEGIN
declare @vstr nvarchar(1000);
declare @vtabname nvarchar(100);
declare @vSchemaName nvarchar(100);
declare @vcolumnName nvarchar(100);
declare c_table cursor scroll for select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE='image' and (table_schema='gpglobal' or table_schema like 'gpcomp%');
declare c_view cursor scroll for select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE='image' and table_schema ='dbo';
declare c_small_table cursor scroll for
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE='varbinary'
and CHARACTER_MAXIMUM_LENGTH=-1
and (table_schema='gpglobal' or table_schema like 'gpcomp%')
and TABLE_NAME not like '%_arch';
open c_table;
fetch first from c_table into @vSchemaName,@vtabname,@vcolumnName;
while @@fetch_status = 0
begin
set @vstr = '';
set @vstr = N'ALTER TABLE '+@vSchemaName+'.'+@vtabname+' ALTER COLUMN '+@vcolumnName+' VARBINARY(MAX)';
print @vstr;
exec SP_EXECUTESQL @vstr;
fetch next from c_table into @vSchemaName,@vtabname,@vcolumnName;
end;
close c_table;
deallocate c_table;
set @vSchemaName = '';
set @vtabname = '';
set @vcolumnName = '';
open c_view;
fetch first from c_view into @vSchemaName,@vtabname,@vcolumnName;
while @@fetch_status = 0
begin
set @vstr = '';
set @vstr = N'DROP VIEW ['+ @vSchemaName+'].['+@vtabname +']';
print @vstr;
exec SP_EXECUTESQL @vstr;
set @vstr = '';
set @vstr = N'CREATE VIEW ['+ @vSchemaName+'].['+@vtabname +'] AS SELECT * FROM GPGLOBAL.'+@vtabname ;
print @vstr;
exec SP_EXECUTESQL @vstr;
fetch next from c_view into @vSchemaName,@vtabname,@vcolumnName;
end;
close c_view;
deallocate c_view;
set @vSchemaName = '';
set @vtabname = '';
set @vcolumnName = '';
open c_small_table;
fetch first from c_small_table into @vSchemaName,@vtabname,@vcolumnName;
while @@fetch_status = 0
begin
set @vstr = '';
set @vstr = N'UPDATE '+@vSchemaName+'.'+@vtabname+' SET '+@vcolumnName+' = '+@vcolumnName;
print @vstr;
exec SP_EXECUTESQL @vstr;
set @vstr = N'ALTER INDEX ALL ON ' +@vSchemaName+'.'+@vtabname+ ' reorganize WITH (LOB_COMPACTION=ON)';
print @vstr;
exec SP_EXECUTESQL @vstr;
fetch next from c_small_table into @vSchemaName,@vtabname,@vcolumnName;
end;
close c_small_table;
deallocate c_small_table;
END
GO
CREATE PROCEDURE UPDATE_NTEXT_TO_VARCHARMAX
AS
BEGIN
declare @vstr nvarchar(1000);
declare @vtabname nvarchar(100);
declare @vSchemaName nvarchar(100);
declare @vcolumnName nvarchar(100);
declare c_table cursor scroll for select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE='ntext' and (table_schema='gpglobal' or table_schema like 'gpcomp%');
declare c_view cursor scroll for select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE='ntext' and table_schema ='dbo';
declare c_small_table cursor scroll for
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE='nvarchar'
and CHARACTER_MAXIMUM_LENGTH=-1
and (table_schema='gpglobal' or table_schema like 'gpcomp%')
and TABLE_NAME not like '%_arch'
and TABLE_NAME not in(
'GPRECL'
,'GPPROB'
,'GPCRDLOGS'
,'GPCUSTBLOB'
,'GPRECLLOG'
,'GPACTV'
);
open c_table;
fetch first from c_table into @vSchemaName,@vtabname,@vcolumnName;
while @@fetch_status = 0
begin
set @vstr = '';
set @vstr = N'ALTER TABLE '+@vSchemaName+'.'+@vtabname+' ALTER COLUMN '+@vcolumnName+' NVARCHAR(MAX)';
print @vstr;
exec SP_EXECUTESQL @vstr;
fetch next from c_table into @vSchemaName,@vtabname,@vcolumnName;
end;
close c_table;
deallocate c_table;
set @vSchemaName = '';
set @vtabname = '';
set @vcolumnName = '';
open c_view;
fetch first from c_view into @vSchemaName,@vtabname,@vcolumnName;
while @@fetch_status = 0
begin
set @vstr = '';
set @vstr = N'DROP VIEW ['+ @vSchemaName+'].['+@vtabname +']';
print @vstr;
exec SP_EXECUTESQL @vstr;
set @vstr = '';
set @vstr = N'CREATE VIEW ['+ @vSchemaName+'].['+@vtabname +'] AS SELECT * FROM GPGLOBAL.'+@vtabname ;
print @vstr;
exec SP_EXECUTESQL @vstr;
fetch next from c_view into @vSchemaName,@vtabname,@vcolumnName;
end;
close c_view;
deallocate c_view;
set @vSchemaName = '';
set @vtabname = '';
set @vcolumnName = '';
open c_small_table;
fetch first from c_small_table into @vSchemaName,@vtabname,@vcolumnName;
while @@fetch_status = 0
begin
set @vstr = '';
set @vstr = N'UPDATE '+@vSchemaName+'.'+@vtabname+' SET '+@vcolumnName+' = '+@vcolumnName;
print @vstr;
exec SP_EXECUTESQL @vstr;
set @vstr = N'ALTER INDEX ALL ON ' +@vSchemaName+'.'+@vtabname+ ' reorganize WITH (LOB_COMPACTION=ON)';
print @vstr;
exec SP_EXECUTESQL @vstr;
fetch next from c_small_table into @vSchemaName,@vtabname,@vcolumnName;
end;
close c_small_table;
deallocate c_small_table;
END
GO
exec UPDATE_IMAGE_TO_VARBINARYMAX;
GO
exec UPDATE_NTEXT_TO_VARCHARMAX;
GO
drop PROCEDURE UPDATE_IMAGE_TO_VARBINARYMAX;
go
drop PROCEDURE UPDATE_NTEXT_TO_VARCHARMAX;
GO
SELECT * FROM gpmainuser.UPGRADE_LOG;
GO
select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE='nvarchar' and TABLE_SCHEMA='GPcomp1'
UPDATE gpcomp1.GARECEIPT_ARCH SET FEED_DATA = FEED_DATA;
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS i , sys.index_columns idx
where DATA_TYPE='ntext' and (table_schema='gpglobal' or table_schema like 'gpcomp%');
select s.name,o.name,I.name,c.name,t.name
from
sys.columns c,
sys.index_columns ic ,
sys.indexes i ,
sys.objects o,
sys.schemas s,
sys.types t
where i.object_id = ic.object_id
and i.index_id = ic.index_id
and i.object_id = o.object_id
and s.schema_id = o.schema_id
and ic.column_id =c.column_id
and t.system_type_id = c.system_type_id
and (s.name ='gpglobal' or s.name like 'gpcomp%')
and o.name ='GPRECL'
and c.name = 'REMARKS'
and t.name = 'ntext'
select distinct s.name,o.name,i.name,c.name,t.name
from
sys.columns c,
sys.index_columns ic ,
sys.indexes i ,
sys.objects o,
sys.schemas s,
sys.types t
where i.object_id = ic.object_id
and i.index_id = ic.index_id
and i.object_id = o.object_id
and s.schema_id = o.schema_id
and ic.column_id = c.column_id
and ic.object_id = c.object_id
and t.system_type_id = c.system_type_id
and (s.name ='gpglobal' or s.name like 'gpcomp%')
and o.name ='GPCRDLOGS'
and t.name = 'ntext'
select * from sys.indexes
select * from sys.index_columns
select * from sys.columns
select * from sys.objects where name ='GPCRDLOGS'
select * from sys.schemas
select * from sysindexes
select * from sys.types
select * from sys.data_spaces;
select * from sysindexes
select object_name(id) tablename,
8*reserved/1024 reservedMB,
rtrim(8*dpages/1024)+'Mb' used,
8*(reserved-dpages)/1024 unused,
8*dpages/1024-rows/1024*minlen/1024 free,rows,*
from sysindexes
where indid=1 AND object_name(id)='GPCRDLOGS'
order by reserved desc
exec sp_spaceused N'GPCOMP1.GPCRDLOGS'
exec sp_refreshview N'gpcomp1.aa'
http://connect.microsoft.com/SQLServer/feedback/details/669621/sp-refreshview-fails-for-views-that-were-created-without-explicitly-qualifying-their-schema-name
http://mitchelsellers.com/blogs/2007/07/27/determing-sql-server-table-size.aspx
http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx
http://msdn.microsoft.com/en-us/library/hh245121.aspx
http://technet.microsoft.com/zh-cn/library/ms162833.aspx
http://technet.microsoft.com/zh-cn/library/ms162808%28v=sql.105%29.aspx
http://technet.microsoft.com/zh-cn/library/ms162843.aspx
http://technet.microsoft.com/zh-cn/library/ms180944.aspx
http://technet.microsoft.com/en-us/library/ms188603%28v=sql.105%29.aspx
http://blogs.msdn.com/b/sqlserverfaq/archive/2008/10/14/sql-server-can-perform-slow-after-changing-the-cost-threshold-for-parallelism.aspx
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/19/tuning-cost-threshold-of-parallelism-from-the-plan-cache.aspx
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
http://my.oschina.net/tangyu/blog/79120
http://sqlmag.com/t-sql/varbinarymax-tames-blob
http://www.poorren.com/sqlserver-2005-varcharmax-nvarcharmax-varbinarymax/
http://blog.csdn.net/xianshengsun/article/details/7657654
http://blog.csdn.net/xianshengsun/article/details/7657551
Determing SQL Server Table Size
