Difference between revisions of "Bulk copy out of text and image data"

From SybaseWiki
Jump to: navigation, search
Line 1: Line 1:
 
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.
 
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 in the database that use text/image data:
+
To determine the tables and columns in the database that use text/image data:
−
  select user_name(uid) as "owner",
+
  select user_name(o.uid) as "owner",
−
         name
+
         o.name as "table",
−
from  sysobjects o
+
        c.name as "column"
−
where  type = "U"
+
        from  sysobjects o,
−
and    exists
+
              syscolumns c
−
        (select 1
+
        where  o.type = "U"
−
                from  syscolumns c
+
        and    o.id = c.id
−
                where o.id = c.id
+
        and   c.type in (34,35)
−
                and   c.type in (34,35))
+
        order  by 1,2,3
  
−
Note: document still under construction
+
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
  
 
[[Category:ASE]]
 
[[Category:ASE]]

Revision as of 12:10, 24 March 2006

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
Retrieved from "http://petersap.nl/SybaseWiki/index.php?title=Bulk_copy_out_of_text_and_image_data&oldid=1410"