Tuesday, December 21, 2010

BCP all the tables in your database

Declare @query nvarchar(100)
Declare @tbl_name varchar(50)
Declare  Del_arch_tbls cursor for

 Select table_name from information_schema.tables where table_schema='schema2'

Open del_arch_tbls
Fetch next from del_arch_tbls into @tbl_name
While @@fetch_status=0
begin
Set @query = 'xp_cmdshell ' +char(39) +
  'bcp databaseA.schema2.'+@tbl_name+
  ' out f:\ppte\schema2-' + @tbl_name+'.dat -SserverA -T -n'
  +char(39)
print @query
exec Sp_executesql @query
Fetch next from del_arch_tbls into @tbl_name
end
Close del_arch_tbls
Deallocate del_arch_tbls

I am updating this post after I found that there's an easy way of doing this.
 simply use  the below to query all the tables in your database.

sp_msforeachtable ' sp_spaceused [?]'

so easy

No comments:

Post a Comment