"pivotting" a table...

Greetings!
I've got a simple list of id's and descriptions that I'd like to rotate so
that each column is the id there exists one row which is the description for
that column.
Basically something like this:
ID Description
--
1 Bob
2 Franks
3 Harry
And I'd like this:
1 2 3
--
Bob Franks Harry
I looked at the PIVOT operator with SQL Server 2005 but this seems more for
aggregating data. What SQL would you use to accomplish this?
Thanks.
Daniel.Dan
CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
GO
INSERT INTO #tmp VALUES (1,'Bob')
INSERT INTO #tmp VALUES(2,'Franks')
INSERT INTO #tmp VALUES(3,'Harry')
SELECT *
FROM #tmp
PIVOT
(
MAX(Description)
FOR ID IN([1],[2],[3])
) AS PVT
"Dan Bass" <na> wrote in message
news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
> Greetings!
> I've got a simple list of id's and descriptions that I'd like to rotate so
> that each column is the id there exists one row which is the description
> for that column.
> Basically something like this:
> ID Description
> --
> 1 Bob
> 2 Franks
> 3 Harry
> And I'd like this:
> 1 2 3
> --
> Bob Franks Harry
>
> I looked at the PIVOT operator with SQL Server 2005 but this seems more
> for aggregating data. What SQL would you use to accomplish this?
> Thanks.
> Daniel.
>|||Thanks Uri!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> Dan
> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
> GO
> INSERT INTO #tmp VALUES (1,'Bob')
> INSERT INTO #tmp VALUES(2,'Franks')
> INSERT INTO #tmp VALUES(3,'Harry')
>
> SELECT *
> FROM #tmp
> PIVOT
> (
> MAX(Description)
> FOR ID IN([1],[2],[3])
> ) AS PVT
> "Dan Bass" <na> wrote in message
> news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
>> Greetings!
>> I've got a simple list of id's and descriptions that I'd like to rotate
>> so that each column is the id there exists one row which is the
>> description for that column.
>> Basically something like this:
>> ID Description
>> --
>> 1 Bob
>> 2 Franks
>> 3 Harry
>> And I'd like this:
>> 1 2 3
>> --
>> Bob Franks Harry
>>
>> I looked at the PIVOT operator with SQL Server 2005 but this seems more
>> for aggregating data. What SQL would you use to accomplish this?
>> Thanks.
>> Daniel.
>|||Dan
More dynamic
DECLARE @.T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@.cols AS NVARCHAR(MAX),
@.y AS INT,
@.sql AS NVARCHAR(MAX);
SET @.cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT id AS y FROM #tmp) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
SET @.sql = N'SELECT *
FROM (SELECT *
FROM #tmp) AS D
PIVOT(MAX(Description) FOR id IN(' + @.cols + N')) AS P;';
EXEC sp_executesql @.sql;
"Dan Bass" <na> wrote in message
news:%23$IJBeqHHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Thanks Uri!
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> Dan
>> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
>> GO
>> INSERT INTO #tmp VALUES (1,'Bob')
>> INSERT INTO #tmp VALUES(2,'Franks')
>> INSERT INTO #tmp VALUES(3,'Harry')
>>
>> SELECT *
>> FROM #tmp
>> PIVOT
>> (
>> MAX(Description)
>> FOR ID IN([1],[2],[3])
>> ) AS PVT
>> "Dan Bass" <na> wrote in message
>> news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
>> Greetings!
>> I've got a simple list of id's and descriptions that I'd like to rotate
>> so that each column is the id there exists one row which is the
>> description for that column.
>> Basically something like this:
>> ID Description
>> --
>> 1 Bob
>> 2 Franks
>> 3 Harry
>> And I'd like this:
>> 1 2 3
>> --
>> Bob Franks Harry
>>
>> I looked at the PIVOT operator with SQL Server 2005 but this seems more
>> for aggregating data. What SQL would you use to accomplish this?
>> Thanks.
>> Daniel.
>>
>

"pivotting" a table...

Greetings!
I've got a simple list of id's and descriptions that I'd like to rotate so
that each column is the id there exists one row which is the description for
that column.
Basically something like this:
ID Description
1 Bob
2 Franks
3 Harry
And I'd like this:
1 2 3
Bob Franks Harry
I looked at the PIVOT operator with SQL Server 2005 but this seems more for
aggregating data. What SQL would you use to accomplish this?
Thanks.
Daniel.
Dan
CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
GO
INSERT INTO #tmp VALUES (1,'Bob')
INSERT INTO #tmp VALUES(2,'Franks')
INSERT INTO #tmp VALUES(3,'Harry')
SELECT *
FROM #tmp
PIVOT
(
MAX(Description)
FOR ID IN([1],[2],[3])
) AS PVT
"Dan Bass" <na> wrote in message
news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
> Greetings!
> I've got a simple list of id's and descriptions that I'd like to rotate so
> that each column is the id there exists one row which is the description
> for that column.
> Basically something like this:
> ID Description
> --
> 1 Bob
> 2 Franks
> 3 Harry
> And I'd like this:
> 1 2 3
> --
> Bob Franks Harry
>
> I looked at the PIVOT operator with SQL Server 2005 but this seems more
> for aggregating data. What SQL would you use to accomplish this?
> Thanks.
> Daniel.
>
|||Thanks Uri!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> Dan
> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
> GO
> INSERT INTO #tmp VALUES (1,'Bob')
> INSERT INTO #tmp VALUES(2,'Franks')
> INSERT INTO #tmp VALUES(3,'Harry')
>
> SELECT *
> FROM #tmp
> PIVOT
> (
> MAX(Description)
> FOR ID IN([1],[2],[3])
> ) AS PVT
> "Dan Bass" <na> wrote in message
> news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
>
|||Dan
More dynamic
DECLARE @.T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@.cols AS NVARCHAR(MAX),
@.y AS INT,
@.sql AS NVARCHAR(MAX);
SET @.cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT id AS y FROM #tmp) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
SET @.sql = N'SELECT *
FROM (SELECT *
FROM #tmp) AS D
PIVOT(MAX(Description) FOR id IN(' + @.cols + N')) AS P;';
EXEC sp_executesql @.sql;
"Dan Bass" <na> wrote in message
news:%23$IJBeqHHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Thanks Uri!
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
>

"pivotting" a table...

Greetings!
I've got a simple list of id's and descriptions that I'd like to rotate so
that each column is the id there exists one row which is the description for
that column.
Basically something like this:
ID Description
--
1 Bob
2 Franks
3 Harry
And I'd like this:
1 2 3
--
Bob Franks Harry
I looked at the PIVOT operator with SQL Server 2005 but this seems more for
aggregating data. What SQL would you use to accomplish this?
Thanks.
Daniel.Dan
CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
GO
INSERT INTO #tmp VALUES (1,'Bob')
INSERT INTO #tmp VALUES(2,'Franks')
INSERT INTO #tmp VALUES(3,'Harry')
SELECT *
FROM #tmp
PIVOT
(
MAX(Description)
FOR ID IN([1],[2],[3])
) AS PVT
"Dan Bass" <na> wrote in message
news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
> Greetings!
> I've got a simple list of id's and descriptions that I'd like to rotate so
> that each column is the id there exists one row which is the description
> for that column.
> Basically something like this:
> ID Description
> --
> 1 Bob
> 2 Franks
> 3 Harry
> And I'd like this:
> 1 2 3
> --
> Bob Franks Harry
>
> I looked at the PIVOT operator with SQL Server 2005 but this seems more
> for aggregating data. What SQL would you use to accomplish this?
> Thanks.
> Daniel.
>|||Thanks Uri!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> Dan
> CREATE TABLE #tmp (ID INT NOT NULL PRIMARY KEY, Description VARCHAR(20))
> GO
> INSERT INTO #tmp VALUES (1,'Bob')
> INSERT INTO #tmp VALUES(2,'Franks')
> INSERT INTO #tmp VALUES(3,'Harry')
>
> SELECT *
> FROM #tmp
> PIVOT
> (
> MAX(Description)
> FOR ID IN([1],[2],[3])
> ) AS PVT
> "Dan Bass" <na> wrote in message
> news:u94YnMqHHHA.4760@.TK2MSFTNGP03.phx.gbl...
>|||Dan
More dynamic
DECLARE @.T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@.cols AS NVARCHAR(MAX),
@.y AS INT,
@.sql AS NVARCHAR(MAX);
SET @.cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT id AS y FROM #tmp) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
SET @.sql = N'SELECT *
FROM (SELECT *
FROM #tmp) AS D
PIVOT(MAX(Description) FOR id IN(' + @.cols + N')) AS P;';
EXEC sp_executesql @.sql;
"Dan Bass" <na> wrote in message
news:%23$IJBeqHHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Thanks Uri!
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:excr1ZqHHHA.3952@.TK2MSFTNGP02.phx.gbl...
>

"Phantom" Stored Procedures?

I have run into a problem where I cannot add a new stored
procedure to the master database because it supposedly
already exists, but I cannot see it in Enterprise
Manager, I cannot see it if I query the sysobjects table
directly for it, and I cannot drop it (I get a message
that nothing by that name exists in the catalog). I'd
like to know what is going on here with this catch-22
thing. It seems to be related to the fact that I moved
the master database over to this server from another one
using BACKUP/RESTORE. The originating server and the
destination server were named differently, and the master
database was moved from the C drive on the original
server to the D drive on the destination server when it
was restored.
I would be most grateful if someone could help me avoid a
rebuild of the master database, or worse, a re-install of
SQL Server altogether to solve this problem.
Mark> I have run into a problem where I cannot add a new stored
> procedure to the master database because it supposedly
> already exists, but I cannot see it in Enterprise
> Manager, I cannot see it if I query the sysobjects table
> directly for it, and I cannot drop it (I get a message
> that nothing by that name exists in the catalog).
Could you tell us the name of the stored procedure you are trying to add,
and why you are trying to add it to the master database?|||1) Are you using the sp_addextendedproc to add it?
2) Have you tried removing it first using sp_dropextendedproc?
3) and you're sure you are adding to master?
"Mark Schmidt" <Me@.spamthis.com> wrote in message
news:006d01c35127$e3ffc370$a101280a@.phx.gbl...
> >--Original Message--
> >> I have run into a problem where I cannot add a new
> stored
> >> procedure to the master database because it supposedly
> >> already exists, but I cannot see it in Enterprise
> >> Manager, I cannot see it if I query the sysobjects
> table
> >> directly for it, and I cannot drop it (I get a message
> >> that nothing by that name exists in the catalog).
> >
> >Could you tell us the name of the stored procedure you
> are trying to add,
> >and why you are trying to add it to the master database?
> >
> >
> >.
> >
> Well, this time it's actually an extended stored
> procedure that a consulting company gave me to effect
> some functionality in one of our applications that uses
> SQL Server. However, I had the same problem upgrading
> another of our servers to SP3a. That time it was system
> stored procedures that couldn't be dropped and re-added
> via the upgrade script because they were already there.
> Both scenarios involve the movement of the master
> database from an older server (which went out the door
> due to its lease expiration) to a newer one. I have
> noticed that seems to be the common denominator.

"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