"Generate SQL Script" functionality

By default, if you use the "Generate SQL Script" menu item when
right-clicking on a table, SQL Server will generate a scripts such as:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[zzTest]
GO
CREATE TABLE [dbo].[zzTest] (
[IDCOL] [int] NOT NULL ,
[DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Simple and straightforward, but presents a problem when the table exists and
contains data. Is there a way to modify SQL Server (e.g., is there a template
somewhere?) such that all future scripts generated using this functionality
would have a check to verify that the table does not contain data, such as in:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 and
exists (select count(*) from dbo.zztest having count(*) = 0))
drop table [dbo].[zzTest]
GO
CREATE TABLE [dbo].[zzTest] (
[IDCOL] [int] NOT NULL ,
[DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
The above is just an example, if there is some other way to NOT drop the
table if if contains data please advise.
Thank you!
--
Brad AshforthAdd:
if count(*) > 0 from table
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> By default, if you use the "Generate SQL Script" menu item when
> right-clicking on a table, SQL Server will generate a scripts such as:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Simple and straightforward, but presents a problem when the table exists
> and
> contains data. Is there a way to modify SQL Server (e.g., is there a
> template
> somewhere?) such that all future scripts generated using this
> functionality
> would have a check to verify that the table does not contain data, such as
> in:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and
> exists (select count(*) from dbo.zztest having count(*) = 0))
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> The above is just an example, if there is some other way to NOT drop the
> table if if contains data please advise.
> Thank you!
> --
> Brad Ashforth|||Hi Jay ... thank you, but my question was not clear. I am looking for a way
to alter SQL Server such that when we use the "Script" function it would
automatically change the output script to inlude this test, which is already
in the code I posted.
This way we can avoid having to manually alter the scripts each time a table
is created/changed.
Thanks
--
Brad Ashforth
"Jay" wrote:
> Add:
> if count(*) > 0 from table
> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> > By default, if you use the "Generate SQL Script" menu item when
> > right-clicking on a table, SQL Server will generate a scripts such as:
> >
> > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[zzTest]
> > GO
> >
> > CREATE TABLE [dbo].[zzTest] (
> > [IDCOL] [int] NOT NULL ,
> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > Simple and straightforward, but presents a problem when the table exists
> > and
> > contains data. Is there a way to modify SQL Server (e.g., is there a
> > template
> > somewhere?) such that all future scripts generated using this
> > functionality
> > would have a check to verify that the table does not contain data, such as
> > in:
> >
> > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> > and
> > exists (select count(*) from dbo.zztest having count(*) = 0))
> > drop table [dbo].[zzTest]
> > GO
> > CREATE TABLE [dbo].[zzTest] (
> > [IDCOL] [int] NOT NULL ,
> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > The above is just an example, if there is some other way to NOT drop the
> > table if if contains data please advise.
> >
> > Thank you!
> >
> > --
> > Brad Ashforth
>
>|||Service pack 2 added some configurable options for scripting. See Tools, Options. I doubt that this
particular feature is there, though. You can wish this for a future version:
http://connect.microsoft.com/sqlserver.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:6124520C-AC9F-4896-B6B6-6A000BF07545@.microsoft.com...
> Hi Jay ... thank you, but my question was not clear. I am looking for a way
> to alter SQL Server such that when we use the "Script" function it would
> automatically change the output script to inlude this test, which is already
> in the code I posted.
> This way we can avoid having to manually alter the scripts each time a table
> is created/changed.
> Thanks
> --
> Brad Ashforth
>
> "Jay" wrote:
>> Add:
>> if count(*) > 0 from table
>> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
>> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
>> > By default, if you use the "Generate SQL Script" menu item when
>> > right-clicking on a table, SQL Server will generate a scripts such as:
>> >
>> > if exists (select * from dbo.sysobjects where id =>> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>> > drop table [dbo].[zzTest]
>> > GO
>> >
>> > CREATE TABLE [dbo].[zzTest] (
>> > [IDCOL] [int] NOT NULL ,
>> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > Simple and straightforward, but presents a problem when the table exists
>> > and
>> > contains data. Is there a way to modify SQL Server (e.g., is there a
>> > template
>> > somewhere?) such that all future scripts generated using this
>> > functionality
>> > would have a check to verify that the table does not contain data, such as
>> > in:
>> >
>> > if exists (select * from dbo.sysobjects where id =>> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
>> > and
>> > exists (select count(*) from dbo.zztest having count(*) = 0))
>> > drop table [dbo].[zzTest]
>> > GO
>> > CREATE TABLE [dbo].[zzTest] (
>> > [IDCOL] [int] NOT NULL ,
>> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > The above is just an example, if there is some other way to NOT drop the
>> > table if if contains data please advise.
>> >
>> > Thank you!
>> >
>> > --
>> > Brad Ashforth
>>|||Hi Tibor ... was this sp for SQL 2000 or 2005? The current projects is using
2000, where I did not find anything under Tools/Options. I'll send it on as a
wish ... thank you!
--
Brad Ashforth
"Tibor Karaszi" wrote:
> Service pack 2 added some configurable options for scripting. See Tools, Options. I doubt that this
> particular feature is there, though. You can wish this for a future version:
> http://connect.microsoft.com/sqlserver.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
> news:6124520C-AC9F-4896-B6B6-6A000BF07545@.microsoft.com...
> > Hi Jay ... thank you, but my question was not clear. I am looking for a way
> > to alter SQL Server such that when we use the "Script" function it would
> > automatically change the output script to inlude this test, which is already
> > in the code I posted.
> >
> > This way we can avoid having to manually alter the scripts each time a table
> > is created/changed.
> >
> > Thanks
> > --
> > Brad Ashforth
> >
> >
> > "Jay" wrote:
> >
> >> Add:
> >>
> >> if count(*) > 0 from table
> >>
> >> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
> >> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> >> > By default, if you use the "Generate SQL Script" menu item when
> >> > right-clicking on a table, SQL Server will generate a scripts such as:
> >> >
> >> > if exists (select * from dbo.sysobjects where id => >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> >> > drop table [dbo].[zzTest]
> >> > GO
> >> >
> >> > CREATE TABLE [dbo].[zzTest] (
> >> > [IDCOL] [int] NOT NULL ,
> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > Simple and straightforward, but presents a problem when the table exists
> >> > and
> >> > contains data. Is there a way to modify SQL Server (e.g., is there a
> >> > template
> >> > somewhere?) such that all future scripts generated using this
> >> > functionality
> >> > would have a check to verify that the table does not contain data, such as
> >> > in:
> >> >
> >> > if exists (select * from dbo.sysobjects where id => >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> >> > and
> >> > exists (select count(*) from dbo.zztest having count(*) = 0))
> >> > drop table [dbo].[zzTest]
> >> > GO
> >> > CREATE TABLE [dbo].[zzTest] (
> >> > [IDCOL] [int] NOT NULL ,
> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > The above is just an example, if there is some other way to NOT drop the
> >> > table if if contains data please advise.
> >> >
> >> > Thank you!
> >> >
> >> > --
> >> > Brad Ashforth
> >>
> >>
> >>
>
>|||Sorry, I'm referring to 2005. No such option in 2000...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:396AC362-1D58-4E5B-8E16-A106DB86A522@.microsoft.com...
> Hi Tibor ... was this sp for SQL 2000 or 2005? The current projects is using
> 2000, where I did not find anything under Tools/Options. I'll send it on as a
> wish ... thank you!
> --
> Brad Ashforth
>
> "Tibor Karaszi" wrote:
>> Service pack 2 added some configurable options for scripting. See Tools, Options. I doubt that
>> this
>> particular feature is there, though. You can wish this for a future version:
>> http://connect.microsoft.com/sqlserver.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
>> news:6124520C-AC9F-4896-B6B6-6A000BF07545@.microsoft.com...
>> > Hi Jay ... thank you, but my question was not clear. I am looking for a way
>> > to alter SQL Server such that when we use the "Script" function it would
>> > automatically change the output script to inlude this test, which is already
>> > in the code I posted.
>> >
>> > This way we can avoid having to manually alter the scripts each time a table
>> > is created/changed.
>> >
>> > Thanks
>> > --
>> > Brad Ashforth
>> >
>> >
>> > "Jay" wrote:
>> >
>> >> Add:
>> >>
>> >> if count(*) > 0 from table
>> >>
>> >> "Brad Ashforth" <banospam@.nospam.nospam> wrote in message
>> >> news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
>> >> > By default, if you use the "Generate SQL Script" menu item when
>> >> > right-clicking on a table, SQL Server will generate a scripts such as:
>> >> >
>> >> > if exists (select * from dbo.sysobjects where id =>> >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>> >> > drop table [dbo].[zzTest]
>> >> > GO
>> >> >
>> >> > CREATE TABLE [dbo].[zzTest] (
>> >> > [IDCOL] [int] NOT NULL ,
>> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > Simple and straightforward, but presents a problem when the table exists
>> >> > and
>> >> > contains data. Is there a way to modify SQL Server (e.g., is there a
>> >> > template
>> >> > somewhere?) such that all future scripts generated using this
>> >> > functionality
>> >> > would have a check to verify that the table does not contain data, such as
>> >> > in:
>> >> >
>> >> > if exists (select * from dbo.sysobjects where id =>> >> > object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
>> >> > and
>> >> > exists (select count(*) from dbo.zztest having count(*) = 0))
>> >> > drop table [dbo].[zzTest]
>> >> > GO
>> >> > CREATE TABLE [dbo].[zzTest] (
>> >> > [IDCOL] [int] NOT NULL ,
>> >> > [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > The above is just an example, if there is some other way to NOT drop the
>> >> > table if if contains data please advise.
>> >> >
>> >> > Thank you!
>> >> >
>> >> > --
>> >> > Brad Ashforth
>> >>
>> >>
>> >>
>>|||Why are you manually scripting tables and then running the DDL on your
server?
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:911CF2CF-D139-40EC-B983-BA2D4C73E81A@.microsoft.com...
> By default, if you use the "Generate SQL Script" menu item when
> right-clicking on a table, SQL Server will generate a scripts such as:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Simple and straightforward, but presents a problem when the table exists
> and
> contains data. Is there a way to modify SQL Server (e.g., is there a
> template
> somewhere?) such that all future scripts generated using this
> functionality
> would have a check to verify that the table does not contain data, such as
> in:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and
> exists (select count(*) from dbo.zztest having count(*) = 0))
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> The above is just an example, if there is some other way to NOT drop the
> table if if contains data please advise.
> Thank you!
> --
> Brad Ashforth|||Here is a bit of T-SQL code from a Data Dictionary program I'm writing.
Shouldn't be too hard to make it produce basic DDL.
--USE msdb Change to DB you want. The proc will be stored there too.
IF EXISTS (SELECT * FROM dbo.sysobjects where id =object_id(N'[dbo].[ScriptTable]') and OBJECTPROPERTY(id, N'IsProcedure') =1)
DROP PROCEDURE [dbo].[ScriptTable]
GO
CREATE PROCEDURE [ScriptTable]
AS
DECLARE @.Table SYSNAME
DECLARE @.TableID INT
DECLARE @.Column SYSNAME
DECLARE @.ColID SMALLINT
DECLARE @.VarType TINYINT
DECLARE @.VarName SYSNAME
DECLARE @.VarLen INT
DECLARE @.ColStatus TINYINT
DECLARE @.StatusDesc CHAR(8)
DECLARE @.IsNullable INT
DECLARE @.ConstraintName SYSNAME
DECLARE @.ColumnDefault CHAR(50)
DECLARE @.Pad1 CHAR(5)
DECLARE @.Pad2 CHAR(5)
DECLARE @.ColNameLen SMALLINT
DECLARE @.ObjName SYSNAME
DECLARE @.ObjType CHAR(4) -- Yes I know it's 2
DECLARE @.PKFK CHAR(11)
DECLARE @.DispPKFK CHAR(6)
DECLARE cTables CURSOR FOR
SELECT so.name, so.id
FROM sysobjects so
WHERE so.xtype = 'U'
AND so.name NOT IN ('dtproperties')
ORDER BY so.name
OPEN cTables
FETCH FROM cTables INTO @.Table, @.TableID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
PRINT 'Table: ' + RTRIM(CONVERT(CHAR(30), @.Table)) --+ ' id: ' +
CONVERT(CHAR(12), @.TableID)
PRINT 'Stored procedures (P), Triggers (TR) and Views (V) that access: "' +
RTRIM(@.Table) + '"'
PRINT 'Type Name'
PRINT '-- --'
DECLARE cProcedures CURSOR FOR
SELECT name, xtype
FROM sysobjects
WHERE id IN (
SELECT id
FROM sysdepends
WHERE depid IN (
SELECT id
FROM sysobjects
WHERE name = @.Table
)
)
OPEN cProcedures
FETCH cProcedures INTO @.ObjName, @.ObjType
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
PRINT @.ObjType + ' ' + CONVERT(CHAR(40), @.ObjName)
FETCH cProcedures INTO @.ObjName, @.ObjType
END
CLOSE cProcedures
DEALLOCATE cProcedures
PRINT ''
PRINT 'PK/FK Column Null Type Default Constraint Comment'
PRINT
'-- -- -- -- -- -- --'
DECLARE cColumns CURSOR FOR
SELECT sc.name, sc.colid, st.name, sc.length, sc.xtype, sc.status,
sc.isnullable
FROM syscolumns sc
INNER JOIN systypes st ON sc.xtype = st.xtype
WHERE id = @.TableID
ORDER BY sc.colorder
OPEN cColumns
FETCH FROM cColumns INTO @.Column, @.ColID, @.VarName, @.VarLen, @.VarType,
@.ColStatus, @.IsNullable
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- Get the column constraint (if available)
SET @.ConstraintName = ''
SELECT @.ConstraintName = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = @.Table
AND COLUMN_NAME = @.Column
-- Get the default value
SET @.ColumnDefault = ' '
SELECT @.ColumnDefault = COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.Table
AND COLUMN_NAME = @.Column
IF @.ColumnDefault IN ('('''')', 'NULL') SET @.ColumnDefault = ' '
IF @.ColumnDefault IS NULL
SET @.ColumnDefault = ' '
ELSE
BEGIN
IF CHARINDEX('(', @.ColumnDefault, 1) = 1
SET @.ColumnDefault = SUBSTRING(@.ColumnDefault, 2, LEN(@.ColumnDefault)-2)
END
-- Find IDENTITY, NOT NULL
SET @.StatusDesc = ' '
IF @.ColStatus = 0
SET @.StatusDesc = ' '
IF @.ColStatus = 128
SET @.StatusDesc = 'IDENTITY'
ELSE IF @.IsNullable = 0
SET @.StatusDesc = 'NOT NULL'
-- Define the pad
SET @.ColNameLen = LEN(RTRIM(@.Column))
IF @.ColNameLen >= 24
SET @.Pad1 = ''
ELSE IF @.ColNameLen >= 16
SET @.Pad1 = ' '
ELSE IF @.ColNameLen >= 8
SET @.Pad1 = ' '
ELSE
SET @.Pad1 = ' '
SET @.ColNameLen = LEN(RTRIM(@.VarName))
IF @.VarType IN (175, 167)
BEGIN
SET @.Pad2 = ''
END
ELSE
BEGIN
IF @.ColNameLen >= 16
SET @.Pad2 = ''
ELSE IF @.ColNameLen >= 8
SET @.Pad2 = ''
ELSE
SET @.Pad2 = ' '
END
-- Is PKFK?
DECLARE cPKFK CURSOR FOR
SELECT TC.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_NAME =KCU.CONSTRAINT_NAME
WHERE TC.TABLE_NAME = @.Table
AND COLUMN_NAME = @.Column
ORDER BY TC.CONSTRAINT_TYPE DESC
OPEN cPKFK
SET @.DispPKFK = ''
-- SET @.PKFK = ''
FETCH cPKFK INTO @.PKFK
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- PRINT '[' + @.PKFK + ']'
IF @.PKFK = 'PRIMARY KEY' SET @.DispPKFK = 'PK'
IF @.PKFK = 'FOREIGN KEY' SET @.DispPKFK = ' FK'
IF @.PKFK = 'UNIQUE ' SET @.DispPKFK = ' U'
-- PRINT 'XXX[' + @.DispPKFK + ']XXX'
-- SET @.PKFK = ''
FETCH cPKFK INTO @.PKFK
END
CLOSE cPKFK
DEALLOCATE cPKFK
IF @.VarType IN (175, 167)
PRINT
-- CONVERT(CHAR(12), @.ColID) +
@.DispPKFK
+ ' '
+ RTRIM(CONVERT(CHAR(30), @.Column)) + RTRIM(@.Pad1)
+ @.StatusDesc + ' '
+ UPPER(RTRIM(CONVERT(CHAR(20), @.VarName))) + '('
+ RTRIM(CONVERT(CHAR(5), @.VarLen)) + ')' + RTRIM(@.Pad2)
+ ' '
+ RTRIM(@.ColumnDefault)
+ ' '
+ @.ConstraintName
-- + ' s: ' + CONVERT(CHAR(3), @.ColStatus) + ': ' + CONVERT(CHAR(3),
@.ColStatus % 8) + 'IsNullable: ' + CONVERT(CHAR(5), @.IsNullable)
ELSE
PRINT
-- CONVERT(CHAR(12), @.ColID) +
@.DispPKFK
+ ' '
+ RTRIM(CONVERT(CHAR(30), @.Column)) + RTRIM(@.Pad1)
+ @.StatusDesc + ' '
+ RTRIM(UPPER(CONVERT(CHAR(20), @.VarName))) + RTRIM(@.Pad2)
+ ' '
+ RTRIM(@.ColumnDefault)
+ ' '
+ @.ConstraintName
-- + 's: ' + CONVERT(CHAR(3), @.ColStatus) + ': ' + CONVERT(CHAR(3),
@.ColStatus % 8) + 'IsNullable: ' + CONVERT(CHAR(5), @.IsNullable)
FETCH FROM cColumns INTO @.Column, @.ColID, @.VarName, @.VarLen, @.VarType,
@.ColStatus, @.IsNullable
END
PRINT ''
CLOSE cColumns
DEALLOCATE cColumns
--BREAK
FETCH FROM cTables INTO @.Table, @.TableID
PRINT
'==============================================================================================================================='
PRINT ''
PRINT ''
END
CLOSE cTables
DEALLOCATE cTables
GO
EXECUTE [ScriptTable]
/*
*/|||Hi Brad,
This feature is not included in both SQL Server 2000 SP4 and SQL Server
2005 SP2. I agree with Tibor that you give Microsoft feedback so that your
voice will be heard by the product team and hope that this feature can be
included in the next release of SQL Server. Appreciate your understanding
that by the original design of SQL Server, it could not think all the
aspects of customers needs. Your feedback is a great impetus to empower
Microsoft product to become better and better.
Now if you are very concerned with this issue, you may consider developing
a tool by yourself to do this. Per my experiences, this should not be a
hard work.
If you have any other questions or concerns, please feel free to let us
know. Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Here's an open source console app you could easily modify to give you
want you are looking for:
http://www.codeplex.com/scriptdb
It will work against any 2000 or 2005 db, requires SMO to be installed
on the client.
On Sep 5, 6:38 am, Brad Ashforth <banos...@.nospam.nospam> wrote:
> By default, if you use the "GenerateSQLScript" menu item when
> right-clicking on a table,SQLServer willgeneratea scripts such as:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Simple and straightforward, but presents a problem when the table exists and
> contains data. Is there a way to modifySQLServer (e.g., is there a template
> somewhere?) such that all future scripts generated using this functionality
> would have a check to verify that the table does not contain data, such as in:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[zzTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 and
> exists (select count(*) from dbo.zztest having count(*) = 0))
> drop table [dbo].[zzTest]
> GO
> CREATE TABLE [dbo].[zzTest] (
> [IDCOL] [int] NOT NULL ,
> [DataCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> The above is just an example, if there is some other way to NOT drop the
> table if if contains data please advise.
> Thank you!
> --
> Brad Ashforth