Is it possible to have the equivalent of a session value? What I'm wanting
to do is get the user to choose a value (item center) on the home page and
use that value to filter all the reports. Then if he wants to see the
reports for a different item center, he must go back to the home page and
choose a different one.
Even better, display the same list of item centers as a filter option at the
top of each report and default it to the currently chosen item center.
Any ideas on how to do this? I saw a post where someone asked if it was
possible to create a global variable to use throughout all reports and noone
answered him. So I'm assuming that it can't be done. But I'm asking anyhow.
Thanks
ToddTodd,
If you create your own web application, you can certainly keep track of some
value and pass it to your reports as one of the parameters. AFAIK, there is
no built-in functionality in the ReportServer or Report Manager web apps.
Ted
"Todd Bright" wrote:
> Is it possible to have the equivalent of a session value? What I'm wanting
> to do is get the user to choose a value (item center) on the home page and
> use that value to filter all the reports. Then if he wants to see the
> reports for a different item center, he must go back to the home page and
> choose a different one.
> Even better, display the same list of item centers as a filter option at the
> top of each report and default it to the currently chosen item center.
> Any ideas on how to do this? I saw a post where someone asked if it was
> possible to create a global variable to use throughout all reports and noone
> answered him. So I'm assuming that it can't be done. But I'm asking anyhow.
> Thanks
> Todd|||So basically each report is just a standalone report, right?
"Ted K" wrote:
> Todd,
> If you create your own web application, you can certainly keep track of some
> value and pass it to your reports as one of the parameters. AFAIK, there is
> no built-in functionality in the ReportServer or Report Manager web apps.
> Ted
> "Todd Bright" wrote:
> > Is it possible to have the equivalent of a session value? What I'm wanting
> > to do is get the user to choose a value (item center) on the home page and
> > use that value to filter all the reports. Then if he wants to see the
> > reports for a different item center, he must go back to the home page and
> > choose a different one.
> >
> > Even better, display the same list of item centers as a filter option at the
> > top of each report and default it to the currently chosen item center.
> >
> > Any ideas on how to do this? I saw a post where someone asked if it was
> > possible to create a global variable to use throughout all reports and noone
> > answered him. So I'm assuming that it can't be done. But I'm asking anyhow.
> >
> > Thanks
> > Todd
Showing posts with label item. Show all posts
Showing posts with label item. Show all posts
Sunday, March 11, 2012
Thursday, March 8, 2012
"Repeat report item with data region on every page" and Export PDF
Why elements with flag enable "Repeat report item with data region on every page" doesn't work with "Export PDF" ?
Anyone can say me why !??!?!
I have tried with ASP.NET and Local Reporting.
Hi Blackat.net,
I'm sorry that i'm so familiar with this issue. However i searched through internet and found a relevated post. Users their also said they cannot use "repeat report item with data region on every page" when exporting to to PDF file. I just wonder if it can give you some hints or do something helpful to you.
The thread is :http://forums.microsoft.com/msdn/showpost.aspx?postid=153680&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0 (Wil Burton raised a qustion which seems resemble yours)
Wil Burton asked:I can't get this to work with a list. TheRepeatOnNewPageproperty for tables works,
butRepeat report item with data region on every page doesn't do anything.
Any help?
bluebunny answered:
Would using a parameter work instead? Did for me.
http://forums.microsoft.com/MSDN/showpost.aspx?postid=339319&siteid=1
Greg
Hope my suggestion helps
Thursday, February 16, 2012
"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
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'
'-- -- -- -- -- -- --'
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)
-- 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
-- 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 ''
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
Saturday, February 11, 2012
"Custom Reports..." grayed out, SP2 (latest) installed
What am I missing, here? The "Custom Reports..." context menu appears, but
is grayed out, and no "Standard Reports" context menu item is there. I've
found no reference to additional steps to get these reports enabled/visible.
Help?
thanks,Where exactly you see all these things ?
Amarnath
"Stan Spotts [Computer Aid, Inc]" wrote:
> What am I missing, here? The "Custom Reports..." context menu appears, but
> is grayed out, and no "Standard Reports" context menu item is there. I've
> found no reference to additional steps to get these reports enabled/visible.
> Help?
> thanks,|||In SQL Server Management Studio.
"Amarnath" wrote:
> Where exactly you see all these things ?
> Amarnath
> "Stan Spotts [Computer Aid, Inc]" wrote:
> > What am I missing, here? The "Custom Reports..." context menu appears, but
> > is grayed out, and no "Standard Reports" context menu item is there. I've
> > found no reference to additional steps to get these reports enabled/visible.
> >
> > Help?
> >
> > thanks,
is grayed out, and no "Standard Reports" context menu item is there. I've
found no reference to additional steps to get these reports enabled/visible.
Help?
thanks,Where exactly you see all these things ?
Amarnath
"Stan Spotts [Computer Aid, Inc]" wrote:
> What am I missing, here? The "Custom Reports..." context menu appears, but
> is grayed out, and no "Standard Reports" context menu item is there. I've
> found no reference to additional steps to get these reports enabled/visible.
> Help?
> thanks,|||In SQL Server Management Studio.
"Amarnath" wrote:
> Where exactly you see all these things ?
> Amarnath
> "Stan Spotts [Computer Aid, Inc]" wrote:
> > What am I missing, here? The "Custom Reports..." context menu appears, but
> > is grayed out, and no "Standard Reports" context menu item is there. I've
> > found no reference to additional steps to get these reports enabled/visible.
> >
> > Help?
> >
> > thanks,
Subscribe to:
Posts (Atom)
