Create a sql stement from existing tables

Is there any way to automatically create a "CREATE TABLE" statment using an
existing table?
I would simply want to execute this statement and it would create, in
another database, a table with the same structure (no data).
Is there a way to do this?
cheers
nathan
hi Nathan,
Nathan wrote:
> Is there any way to automatically create a "CREATE TABLE" statment
> using an existing table?
> I would simply want to execute this statement and it would create, in
> another database, a table with the same structure (no data).
> Is there a way to do this?
not directly... you can use tools like Enterlirse Manager (if you are
licensed to) or even free tools like QALite
(http://www.rac4sql.net/qalite_main.asp) to generate thos kind of DDL...
but with some efforts you can dig into the INFORMATION_SCHEMA views to
output the desired result...
start with
http://msdn.microsoft.com/library/de...a-iz_87w3.asp,
INFORMATION_SCHEMA.COLUMNS..
this is a 5 minutes sample... if you like more deep details such as
constraints, keys, index and the like you have to expand your search on the
other INFORMATION_SCHEMA views..
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE dbo.table1 (
Id int NOT NULL PRIMARY KEY ,
vc varchar(10) NULL ,
c char(1) NOT NULL DEFAULT ('a') ,
i int ,
d decimal(18,4) NOT NULL DEFAULT 0
)
GO
DECLARE @.COLUMN_NAME nvarchar(128),
@.COLUMN_DEFAULT nvarchar(4000),
@.IS_NULLABLE varchar(3),
@.DATA_TYPE nvarchar(128),
@.NUMERIC_PRECISION tinyint,
@.NUMERIC_SCALE int,
@.COLLATION_NAME nvarchar(128),
@.CHARACTER_MAXIMUM_LENGTH smallint
DECLARE @.cmd varchar(8000)
SET @.cmd = ''
DECLARE t CURSOR FOR
SELECT c.COLUMN_NAME, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.DATA_TYPE,
c.NUMERIC_PRECISION, c.NUMERIC_SCALE, c.COLLATION_NAME,
c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'table1'
ORDER BY c.ORDINAL_POSITION
OPEN t
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF DATALENGTH(@.cmd) <> 0 SET @.cmd = @.cmd + ' ,' + CHAR(10)
SET @.cmd = @.cmd + CHAR(9) + QUOTENAME( @.COLUMN_NAME )+ ' ' + @.DATA_TYPE + '
'
IF @.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar')
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.CHARACTER_MAXIMUM_LENGTH) + ') '
IF @.DATA_TYPE = 'decimal' OR @.DATA_TYPE = 'numeric'
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.NUMERIC_PRECISION) + ', ' +
CONVERT(varchar, @.NUMERIC_SCALE) + ') '
IF NOT @.COLUMN_DEFAULT IS NULL
SET @.cmd = @.cmd + 'DEFAULT ' + @.COLUMN_DEFAULT + ' '
IF NOT @.COLLATION_NAME IS NULL
SET @.cmd = @.cmd + 'COLLATE ' + @.COLLATION_NAME + ' '
IF @.IS_NULLABLE = 'YES'
SET @.cmd = @.cmd + 'NULL '
ELSE
SET @.cmd = @.cmd + 'NOT NULL '
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
END
CLOSE t
DEALLOCATE t
SET @.cmd = 'CREATE TABLE ' + QUOTENAME('dbo') + '.' + QUOTENAME('table1') +
' (' + CHAR(10)
+ @.cmd + CHAR(10)
+ CHAR(9) + ')'
SELECT @.cmd
GO
DROP TABLE dbo.table1
--<--
CREATE TABLE [dbo].[table1] (
[Id] int NOT NULL ,
[vc] varchar (10) COLLATE Latin1_General_CI_AS NULL ,
[c] char (1) DEFAULT ('a') COLLATE Latin1_General_CI_AS NOT NULL ,
[i] int NULL ,
[d] decimal (18, 4) DEFAULT (0) NOT NULL
)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

create a log file of a script

I'm am new to using SQL Server. In Oracle, when I create scripts to
manipulate data, I have a log file automatically created from within the
script by issuing the SPOOL command. Is there a similar functionality in SQL
server? besides the log file which records each transaction. Is there any
other way to run a script (automatically) besides cut & paste into SQL query
analyzer?
ThanksLee Ann,
The transaction log for the database is the only automatic record of the
transactions that I'm aware of. You could implement a custom solution using
a trigger/audit table or use a third-party log viewing tool. If you're
trying to test out the command prior to having it automatically commit
(admin type stuff) you can embed the code in a user-defined transaction
(BEGIN TRAN...COMMIT TRAN/ROLLBACK TRAN).

> Is there any other way to run a script (automatically)
You can use schedule a job: T-SQL, schedule a job - DTS, use OSQL and
Windows scheduler, auto-startup procs.
HTH
Jerry
"Lee Ann" <LeeAnn@.discussions.microsoft.com> wrote in message
news:1CA21C9E-3E8B-4265-B7A3-EBF26811AE94@.microsoft.com...
> I'm am new to using SQL Server. In Oracle, when I create scripts to
> manipulate data, I have a log file automatically created from within the
> script by issuing the SPOOL command. Is there a similar functionality in
> SQL
> server? besides the log file which records each transaction. Is there any
> other way to run a script (automatically) besides cut & paste into SQL
> query
> analyzer?
> Thanks|||Lee Ann,
One other approach would be to add auditing code (INSERTs and PRINTs) into
your original script.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:Ovq8PAqyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Lee Ann,
> The transaction log for the database is the only automatic record of the
> transactions that I'm aware of. You could implement a custom solution
> using a trigger/audit table or use a third-party log viewing tool. If
> you're trying to test out the command prior to having it automatically
> commit (admin type stuff) you can embed the code in a user-defined
> transaction (BEGIN TRAN...COMMIT TRAN/ROLLBACK TRAN).
>
> You can use schedule a job: T-SQL, schedule a job - DTS, use OSQL and
> Windows scheduler, auto-startup procs.
> HTH
> Jerry
> "Lee Ann" <LeeAnn@.discussions.microsoft.com> wrote in message
> news:1CA21C9E-3E8B-4265-B7A3-EBF26811AE94@.microsoft.com...
>|||Thanks Jerry...I will go and read up on your suggestions and then try it
out.
Lee Ann
"Jerry Spivey" wrote:

> Lee Ann,
> One other approach would be to add auditing code (INSERTs and PRINTs) into
> your original script.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:Ovq8PAqyFHA.1856@.TK2MSFTNGP12.phx.gbl...
>
>sql

CR8.5 and VB6 - I dont want it to create the form!

Every time I add a new Crystal Report to my VB6 project it used to ask if I wanted to add a form with the viewer control on it automatically (it also asked something else, but I can't remember what). I got sick of hitting "No" then "OK" so I checked the "don't ask me this question again" style box.

Now, every time I add a report it also adds a form to my project with the viewer control. I don't want it to do this, as it's annoying removing the useless form and it is screwing with my source control. And it does it even though when i checked the "don't ask me... " i told it not to create the form.

So I've checked all the settings I can find and I can't find anything to stop it happening. Does anyone have a quick fix to this before I go trawling through the registry to find a solution?update - i trawled the registry, found some likely candidates, but none of them worked. It's real frustrating, so any help will be appreciated, but I can live with it (I've lived with worse)