Bulk copy out of text and image data
From SybaseWiki
Revision as of 15:25, 4 May 2006 by 1146752951 (Talk | contribs)
When you want to export a database with bcp, you need to check if there is any text or image data (blobs) in the database, check the maximum size of these blobs and use this to specify the value for the -T argument in bcp command. Here are some usefull queries.
To determine the tables and columns in the database that use text/image data:
select user_name(o.uid) as "owner",
o.name as "table",
c.name as "column"
from sysobjects o,
syscolumns c
where o.type = "U"
and o.id = c.id
and c.type in (34,35)
order by 1,2,3
Once you know which columns are used to store blobs, run the following query to select the maximum length of a blob in bytes:
select max(datalength(<column_name>)) from <table_name>
Here is a stored procedure to check a database. You need to use ASE 12.0 or later for this.
create proc sp_blobsize
as
set nocount on
declare myCursor cursor for
select user_name(o.uid),o.name,c.name
from sysobjects o,syscolumns c
where o.type = "U" and o.id = c.id and c.type in (34,35)
order by 1,2,3
declare @user varchar(30),@table varchar(30),@column varchar(30),@cmd varchar(512),@first tinyint,@db varchar(30)
select @first = 0,@db = db_name()
print "This utility looks for text and image data in tables and"
print "calculates the maximum size in bytes."
print "Checking database %1!",@db
print ""
open myCursor
if @@error != 0 return 1
fetch myCursor into @user, @table, @column
while @@sqlstatus = 0
begin
if @first = 0
begin
print "Output is shown in the format owner.table.column - maximum size"
print ""
select @first = 1
end
select @cmd = "declare @l int select @l=max(datalength(" + rtrim(@column) + ")) from " + rtrim(@user) + "."
+ rtrim(@table) + " print '%1!.%2!.%3! - %4!','" + @user + "','" + @table + "','" + @column + "',@l"
exec (@cmd)
if @@error != 0 break
fetch myCursor into @user, @table, @column
end
close myCursor
deallocate cursor myCursor
if @first = 0 print "No text/image data found in this database."
return 0
go
