CODEWITHSUNDEEP
sql-servert-sql
Meysam Javadi
Meysam Javadi

Reputation: 1404

generate script of content of one table as a .sql file(via code)

is this possible that i create a stored procedure that generates content of defined table to a path that i passed as its(stored procedure) parameter?

Upvotes: 0

Views: 271

Answers (3)

TheVillageIdiot
TheVillageIdiot

Reputation: 40507

try this:

create proc spOutputData @tbl varchar(500)
as

declare @cmd varchar(4000)

SELECT @cmd = 'bcp ' + @tbl + ' out c:\OutFile.txt -c  -q -Shpas -T'

exec xp_cmdshell @cmd

TEST:

spOutputData 'master.dbo.syslogins'

Upvotes: 0

Sparky
Sparky

Reputation: 15075

Try the code below

create procedure dbo.ShowAllRows (@tabName VARCHAR(200) )
as
begin
    declare @Sql    NVARCHAR(2000)
    set @Sql = 'select * FROM '+@tabName
    EXEC (@sql)
end
go
exec ShowAllRows  'sys.configurations'

I missed the path part, I assume you want the above type of code, with a second parameter, i.e. @outputFileName

If your SQL-server has access to the file path and you can run XP_CMDShell, you can do the following...

create procedure dbo.ShowAllRows (@tabName VARCHAR(200),@outPath VARCHAR(200) )
as
begin
    declare @Sql    NVARCHAR(2000)

    set @sql = 'bcp '+@tabName+' out '+@outPath+' -U<user> -P<password> '
    print @sql
    EXEC xp_cmdShell @sql
end

You can also use the -T for trusted connection if you don't want the user name and password in the procedure

Upvotes: 1

Sparky
Sparky

Reputation: 15075

If you mean the table structure, here is the SQL to get you started...

select column_name,data_type,is_nullable
from information_schema.columns
where table_name = '<table name>'
order by ordinal_position

In a procedure, simply do

create produce ShowColumnsforTable( @tabName VARCHAR(200) )
as
begin
    select column_name,data_type,is_nullable
    from information_schema.columns
    where table_name = @tabName
    order by ordinal_position

end

Upvotes: 0

Related Questions