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 tab开发者_StackOverflowle to a path that i passed as its(stored procedure) parameter?

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

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

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'