copy table script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

copy table script

Author

Topic

carumuga
Posting Yak Master

174 Posts

Posted - 2009-10-06 : 05:16:41

Hi,

My requirement is, I need to copy the table script from one server to another. Be clear that, no data should be copied only structured need to be copied for any given tables.

Thanks in advance.

carumuga
Posting Yak Master

174 Posts

Posted - 2009-10-06 : 05:18:43

There is a change in the requirement. The table structure (any given table) should be copied to the file system rather than to the server.

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-06 : 06:23:07

create Procedure getTableStructureToFile(@objName Varchar(100),@TargetFile Varchar(100))
AS
BEGIN
Declare
@StrSql Varchar(1000),
@SqlCmd Varchar(1000)

Set @StrSql='sp_help '''+ @objName + ''''

Set @SqlCmd='sqlcmd -S localhost -U sa -P sapassword -Q "'+ @StrSql +'" -o "'+@TargetFile+'"'
--print @SqlCmd
execute xp_cmdshell @SqlCmd
END

exec getTableStructureToFile 'sys.objects','C:\test.txt'

/*
ServerName,Username,Password are not paremeterized.you can do so or change it in the following line accordingly
Set @SqlCmd='sqlcmd -S <ServerName> -U <UserName> -P <password> -Q "'+ @StrSql +'" -o "'+@TargetFile+'"'

*/

carumuga
Posting Yak Master

174 Posts

Posted - 2009-10-06 : 08:36:08

One correction buddy, we need to write the CREATE TABLE Structure not the property (sp_help) of the object.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources