January 14, 2009 at 4:12 am
Hello all,
we are currently looking for a way to automatically (e.g. based on a script) export the structure of a table so that in the end we get a create table statement in a flatefile.
Any hints on how to do this or an example script available?
Thanks!
JM
January 14, 2009 at 4:28 am
This is not a script, but i use this utility quite a bit to generate scripts from SQL server.
http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx
January 14, 2009 at 4:28 am
Try the following link on MSDN forum:
-Vikas Bindra
January 15, 2009 at 8:34 am
I found Re Gates Tool belt DOC 2.0 to have all the information that you need. Download the trial. See if that helps.
https://www.red-gate.com/dynamic/downloads/downloadform.aspx?download=sqldoc This has a 14 day trial
January 18, 2009 at 8:26 am
Thank you - but this is supposted to work as an automated script as this job should run on it's own as part of an archiving job (to which I want to add the create table statement of the specific table) in order to rebuilt it somewhere else...
So I'm still looking for a script....
January 18, 2009 at 8:48 am
You can write one yourself, based off sys.tables, sys.column and sys.types. It's not difficult, thought it is a it tedious to write (I don't have one, but I did similar recently to auto-generate merge statements and insert statements)
To get you started (completely untested)
DECLARE @TableName sysname, @Script varchar(max)
Set @Tablename = 'MyTable' -- the table that you want to extract.
SELECT @Script = 'CREATE TABLE ' + @TableName + ' ('
SELECT @Script = @Script + col.name + ' ' + type.name + ','
FROM sys.columns col inner join sys.types type on col.user_type_id = type.user_type_id
where col.object_id = object_id(@TableName)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2009 at 9:03 am
January 19, 2009 at 5:58 am
January 22, 2009 at 4:38 am
The following code will script out all tables in a database. It uses sql-dmo so if you are using sql server 2005, ensure that the backwards compatibility pack is installed and that ole is enabled.
Notes:
Connect to the relevant server and database before running.
In the cursor deifinition, I've left in all of the object types so you can uncomment the 'in' statement if you want to script all objects (triggers, views, procs, functions etc)
It will not script and encrypted object
Does not work with varchar(max) so you are limited to 8K per ojbect definition
DECLARE @cDBName varchar(30)
DECLARE @iServerObject int
DECLARE @iError int
DECLARE @cReturn varchar(200)
DECLARE @cSQLStr varchar(200)
DECLARE @cObjName varchar(128)
DECLARE @cObjParent varchar(128)
DECLARE @cContext varchar(255)
DECLARE @cSource varchar(255)
DECLARE @cDescription varchar(255)
DECLARE @cDefinition varchar(8000) ---issues with using varchar(max)
DECLARE @cCollection varchar(30)
DECLARE @iObjectDefinition int
declare @cServerName varchar(30)
SET NOCOUNT ON
set @cServerName = 'name_of_server'
set @cDBName = 'name_of_database'
-- Create an object that points to the SQL Server
SELECT @cContext = 'create dmo object'
EXEC @iError = sp_OACreate 'SQLDMO.SQLServer', @iServerObject OUT
IF @iError <> 0
BEGIN
EXEC @iError = sp_OAGetErrorInfo @iServerObject, @cSource OUT, @cDescription OUT
goto exit_script
END
-- Connect to the SQL Server using a trusted connection
SELECT @cContext = 'set integrated security ' + @cServerName
EXEC @iError = sp_OASetProperty @iServerObject, LoginSecure, 1
IF @iError <> 0
BEGIN
EXEC @iError = sp_OAGetErrorInfo @iServerObject, @cSource OUT, @cDescription OUT
goto exit_script
END
EXEC @iError = sp_OAMethod @iServerObject,'Connect'
IF @iError <> 0
BEGIN
EXEC @iError = sp_OAGetErrorInfo @iServerObject, @cSource OUT, @cDescription OUT
goto exit_script
END
-- Verify the connection
EXEC @iError = sp_OAMethod @iServerObject, 'VerifyConnection', @cReturn OUT
IF @iError <> 0
BEGIN
EXEC @iError = sp_OAGetErrorInfo @iServerObject, @cSource OUT, @cDescription OUT
goto exit_script
END
--retrieve the object using a cursor
DECLARE curObjDef CURSOR SCROLL LOCAL FOR
SELECTid,
name,
CASE xType
WHEN 'U' THEN 'Tables'
WHEN 'TR' THEN 'Triggers'
WHEN 'V' THEN 'Views'
WHEN 'FN' THEN 'Userdefinedfunctions'
WHEN 'TF' THEN 'Userdefinedfunctions'
WHEN 'IF' THEN 'Userdefinedfunctions'
WHEN 'P' THEN 'Storedprocedures'
WHEN 'D' THEN 'Defaults'
END AS Collection,
Parent_Obj
FROMsysobjects
WHERExType = 'U' --IN('U', 'TR', 'V', 'FN', 'TF', 'IF', 'P', 'D')
OPEN curObjDef
FETCH FIRST FROM curObjDef INTO @iObjectDefinition, @cObjName, @cCollection, @cObjParent
WHILE @@fetch_status <> -1
BEGIN
IF @cCollection = 'Triggers'
SET @cSQLStr = 'Databases("'+ @cDBName +'").tables("'
+ RTRIM(UPPER(@cObjParent))+'").'+ @cCollection + '("'
+ RTRIM(UPPER(@cObjName))+'").Script'
ELSE
SET @cSQLStr = 'Databases("'+ @cDBName +'").'+ @cCollection + '("'
+ RTRIM(UPPER(@cObjName))+'").Script'
EXEC @iError = sp_OAMethod @iServerObject, @cSQLStr, @cDefinition OUTPUT, 4
PRINT @cDefinition -- output to screen
FETCH NEXT FROM curObjDef INTO @iObjectDefinition, @cObjName, @cCollection, @cObjParent
END
CLOSE curObjDef
DEALLOCATE curObjDef
--Close connection
EXEC @iError = sp_OAMethod @iServerObject, 'Disconnect'
IF @iError <> 0
BEGIN
EXEC @iError = sp_OAGetErrorInfo @iServerObject, @cSource OUT, @cDescription OUT
goto exit_script
END
-- Destroy the object
EXEC @iError = sp_OADestroy @iServerObject
IF @iError <> 0
BEGIN
EXEC @iError = sp_OAGetErrorInfo @iServerObject, @cSource OUT, @cDescription OUT
goto exit_script
END
exit_script:
print @iError
January 22, 2009 at 3:20 pm
Its not working for me.
set @cServerName = '[ABC-1C98DDDFF87\SQL2000]'
set @cDBName = 'master'
What else do I need to set as I am logged in to this instance and still getting sql server does not exist message when I try to run the script.
Plz help!
MJ
January 22, 2009 at 4:59 pm
SQL Server Magazine just did a review of Sql Scripter 2.0 this month. The review is at http://www.sqlmag.com/Article/ArticleID/100565/sql_server_100565.html
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
January 23, 2009 at 3:22 am
I just retested on a server with running 3 SQL Server 2005 instances and it ran successfully on all 3.
Do you have the backwards compatibility pack installed and have you enabled ole automation?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply