easy way to mark all the fk's and triggers NFR?

Is there a script somehwhere which would alter all constraints and
recreate/alter triggers with NFR property set to 1?
or
would one of you guys know of an easy way to do it?
Thank you.
Tejas.
Following should help. PLEASE TEST IT BEFORE USING IT IN A REAL ENVIRONMENT.
You may uncomment the commented lines if trying to do the same for individual
tables.
SET NOCOUNT ON
GO
DECLARE@.tbl_namevarchar(50)
--SELECT@.tbl_name = 'tbl_name'
/*
Creating the command to drop constraints on the table
*/
SELECT'ALTER TABLE ' + object_name(fkeyid) + ' DROP CONSTRAINT ' +
object_name(constid) + CHAR(10) + 'GO' + CHAR(10)
FROMsysreferences
-- WHERE object_name(fkeyid) = @.tbl_name
-- ORobject_name(rkeyid) = @.tbl_name
ORDER BY object_name(fkeyid)
/*
Creating the command to re-create all the dropped constraints
*/
SELECT'ALTER TABLE ' + object_name(a.fkeyid) + ' ADD CONSTRAINT ' +
object_name(constid) + ' FOREIGN KEY (' + b.name + ') REFERENCES ' +
object_name(a.rkeyid) + '(' + c.name + ') NOT FOR REPLICATION' + CHAR(10) +
'GO' + CHAR(10)
FROM sysforeignkeys a,
syscolumns b,
syscolumns c
WHERE a.fkey = b.colid
AND a.fkeyid = b.id
AND a.rkey = c.colid
AND a.rkeyid = c.id
-- AND (object_name(a.rkeyid) = @.tbl_nameOR
-- object_name(a.fkeyid) = @.tbl_name)
ORDER BY object_name(a.fkeyid)
GO
"Tejas Parikh" wrote:

> Is there a script somehwhere which would alter all constraints and
> recreate/alter triggers with NFR property set to 1?
> or
> would one of you guys know of an easy way to do it?
> Thank you.
> Tejas.

Dynamic SQL another example

In several messages that I post early I need help for create Dynamic SQL or
that I trying to do.
Here is an example of my store procedure
ALTER PROCEDURE dbo.Test
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
AS
SET LANGUAGE us_english
SET NOCOUNT ON
declare @.Colum sysname
declare @.MyDate nvarchar(10)
set @.MyDate = '2004-05-05'
set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
print '---'
print 'The following expression no generate an error but not return me'
print 'anything rows. ( Both are String )'
print '---'
-- Both are String
print 'Expression:'
print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
print 'Result:'
print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
print '================================================================'
print
'----'
print 'Thi following expression generate an error:(to have types mismatch).'
print
'----'
-- To have types mismatch.
print 'Expression:'
print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
print 'Result:'
print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
print '================================================================'
SET NOCOUNT OFF
RETURN
-- The out --
Running dbo."Test".
---
The following expression no generate an error but not return me
anything rows. ( Both are String )
---
Expression:
@.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
Result:
FechaMie>=2004-05-05
================================================================
----
Thi following expression generate an error:(to have types mismatch).
----
Expression:
@.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
Result:
Syntax error converting datetime from character string.
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE = Finished running dbo."Test".I just posted a reply to this. No need to repost.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> In several messages that I post early I need help for create Dynamic SQL or
> that I trying to do.
> Here is an example of my store procedure
> ALTER PROCEDURE dbo.Test
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> AS
> SET LANGUAGE us_english
> SET NOCOUNT ON
> declare @.Colum sysname
> declare @.MyDate nvarchar(10)
> set @.MyDate = '2004-05-05'
> set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
> print '---'
> print 'The following expression no generate an error but not return me'
> print 'anything rows. ( Both are String )'
> print '---'
> -- Both are String
> print 'Expression:'
> print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
> print 'Result:'
> print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> print '================================================================'
> print
> '----'
> print 'Thi following expression generate an error:(to have types mismatch).'
> print
> '----'
> -- To have types mismatch.
> print 'Expression:'
> print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
> print 'Result:'
> print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> print '================================================================'
> SET NOCOUNT OFF
> RETURN
>
> -- The out --
>
> Running dbo."Test".
> ---
> The following expression no generate an error but not return me
> anything rows. ( Both are String )
> ---
> Expression:
> @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> Result:
> FechaMie>=2004-05-05
> ================================================================>
> ----
> Thi following expression generate an error:(to have types mismatch).
> ----
> Expression:
> @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> Result:
> Syntax error converting datetime from character string.
> No rows affected.
> (0 row(s) returned)
> @.RETURN_VALUE => Finished running dbo."Test".
>
>|||The following code represents my problem. It's using Northwind database. You
can copy and paste in the SQL analyzer directly and run so you can see the
problem.
My question is: How Can I build a SQL statement but doing replacement of
certain values on fly.
The causes are error data mismatch.
NOTE: I try sp_executeslq and this entire thing.
ALTER PROCEDURE dbo.StoredProcedure1
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
AS
set language us_English
set nocount on
declare @.BirthDatePos int
declare @.colum sysname -- sysname is equal to nvarchar(128)
/*
* ----
* When @.DateFrom and @.DateTo are datetime the SQL-Server
* not run. I get the following error message:
* Syntax error converting datetime from character string.
* ----
*/
declare @.DateFrom datetime
declare @.DateTo datetime
/*
* ---
* When @.DateFrom and @.DateTo are both characters the SQL-Server
* run but not return anything.
* ---
*/
--declare @.DateFrom nvarchar(8)
--declare @.DateTo nvarchar(8)
/* Creating my Dynamic SQL: */
set @.DateFrom = '20040505'
set @.DateTo = '20040505'
set @.BirthDatePos = 6
set @.colum = col_name(object_id('Employees'),@.BirthDatePos)
SELECT BirthDate
FROM Employees
WHERE (@.colum >= @.DateFrom) AND (@.colum <= @.DateTo)
set nocount off
RETURN
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#plMbyVMEHA.624@.TK2MSFTNGP11.phx.gbl...
> I just posted a reply to this. No need to repost.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Mario Reiley" <mreiley@.cantv.net> wrote in message
news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> > In several messages that I post early I need help for create Dynamic SQL
or
> > that I trying to do.
> >
> > Here is an example of my store procedure
> >
> > ALTER PROCEDURE dbo.Test
> > /*
> > (
> > @.parameter1 datatype = default value,
> > @.parameter2 datatype OUTPUT
> > )
> > */
> > AS
> >
> > SET LANGUAGE us_english
> > SET NOCOUNT ON
> >
> > declare @.Colum sysname
> > declare @.MyDate nvarchar(10)
> >
> > set @.MyDate = '2004-05-05'
> > set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
> >
> > print '---'
> > print 'The following expression no generate an error but not return me'
> > print 'anything rows. ( Both are String )'
> > print '---'
> >
> > -- Both are String
> > print 'Expression:'
> > print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
> > print 'Result:'
> > print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> >
> > print '================================================================'
> >
> > print
> >
'----'
> > print 'Thi following expression generate an error:(to have types
mismatch).'
> > print
> >
'----'
> >
> > -- To have types mismatch.
> > print 'Expression:'
> > print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
> > print 'Result:'
> > print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> >
> > print '================================================================'
> >
> > SET NOCOUNT OFF
> > RETURN
> >
> >
> > -- The out --
> >
> >
> > Running dbo."Test".
> >
> > ---
> > The following expression no generate an error but not return me
> > anything rows. ( Both are String )
> > ---
> > Expression:
> > @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> > Result:
> > FechaMie>=2004-05-05
> > ================================================================> >
> >
> >
> > ----
> > Thi following expression generate an error:(to have types mismatch).
> > ----
> > Expression:
> > @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> > Result:
> > Syntax error converting datetime from character string.
> > No rows affected.
> > (0 row(s) returned)
> > @.RETURN_VALUE => > Finished running dbo."Test".
> >
> >
> >
>|||I posted a reply to your other thread about 10 minutes ago.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:Oh1KT7eMEHA.624@.TK2MSFTNGP11.phx.gbl...
> The following code represents my problem. It's using Northwind database. You
> can copy and paste in the SQL analyzer directly and run so you can see the
> problem.
>
> My question is: How Can I build a SQL statement but doing replacement of
> certain values on fly.
>
> The causes are error data mismatch.
>
> NOTE: I try sp_executeslq and this entire thing.
>
>
> ALTER PROCEDURE dbo.StoredProcedure1
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> AS
>
> set language us_English
> set nocount on
>
> declare @.BirthDatePos int
> declare @.colum sysname -- sysname is equal to nvarchar(128)
>
> /*
> * ----
> * When @.DateFrom and @.DateTo are datetime the SQL-Server
> * not run. I get the following error message:
> * Syntax error converting datetime from character string.
> * ----
> */
> declare @.DateFrom datetime
> declare @.DateTo datetime
>
> /*
> * ---
> * When @.DateFrom and @.DateTo are both characters the SQL-Server
> * run but not return anything.
> * ---
> */
> --declare @.DateFrom nvarchar(8)
> --declare @.DateTo nvarchar(8)
>
> /* Creating my Dynamic SQL: */
> set @.DateFrom = '20040505'
> set @.DateTo = '20040505'
> set @.BirthDatePos = 6
> set @.colum = col_name(object_id('Employees'),@.BirthDatePos)
>
> SELECT BirthDate
> FROM Employees
> WHERE (@.colum >= @.DateFrom) AND (@.colum <= @.DateTo)
> set nocount off
> RETURN
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#plMbyVMEHA.624@.TK2MSFTNGP11.phx.gbl...
> > I just posted a reply to this. No need to repost.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "Mario Reiley" <mreiley@.cantv.net> wrote in message
> news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> > > In several messages that I post early I need help for create Dynamic SQL
> or
> > > that I trying to do.
> > >
> > > Here is an example of my store procedure
> > >
> > > ALTER PROCEDURE dbo.Test
> > > /*
> > > (
> > > @.parameter1 datatype = default value,
> > > @.parameter2 datatype OUTPUT
> > > )
> > > */
> > > AS
> > >
> > > SET LANGUAGE us_english
> > > SET NOCOUNT ON
> > >
> > > declare @.Colum sysname
> > > declare @.MyDate nvarchar(10)
> > >
> > > set @.MyDate = '2004-05-05'
> > > set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
> > >
> > > print '---'
> > > print 'The following expression no generate an error but not return me'
> > > print 'anything rows. ( Both are String )'
> > > print '---'
> > >
> > > -- Both are String
> > > print 'Expression:'
> > > print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
> > > print 'Result:'
> > > print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> > >
> > > print '================================================================'
> > >
> > > print
> > >
> '----'
> > > print 'Thi following expression generate an error:(to have types
> mismatch).'
> > > print
> > >
> '----'
> > >
> > > -- To have types mismatch.
> > > print 'Expression:'
> > > print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
> > > print 'Result:'
> > > print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> > >
> > > print '================================================================'
> > >
> > > SET NOCOUNT OFF
> > > RETURN
> > >
> > >
> > > -- The out --
> > >
> > >
> > > Running dbo."Test".
> > >
> > > ---
> > > The following expression no generate an error but not return me
> > > anything rows. ( Both are String )
> > > ---
> > > Expression:
> > > @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> > > Result:
> > > FechaMie>=2004-05-05
> > > ================================================================> > >
> > >
> > >
> > > ----
> > > Thi following expression generate an error:(to have types mismatch).
> > > ----
> > > Expression:
> > > @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> > > Result:
> > > Syntax error converting datetime from character string.
> > > No rows affected.
> > > (0 row(s) returned)
> > > @.RETURN_VALUE => > > Finished running dbo."Test".
> > >
> > >
> > >
> >
> >
>|||Mr. Tibor You code was enough clear I want give thanks to you publicly.
yours advisor was profit.
I resolve my problem....!
MArio
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#I2FIrfMEHA.2500@.TK2MSFTNGP12.phx.gbl...
> I posted a reply to your other thread about 10 minutes ago.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Mario Reiley" <mreiley@.cantv.net> wrote in message
news:Oh1KT7eMEHA.624@.TK2MSFTNGP11.phx.gbl...
> > The following code represents my problem. It's using Northwind database.
You
> > can copy and paste in the SQL analyzer directly and run so you can see
the
> > problem.
> >
> >
> >
> > My question is: How Can I build a SQL statement but doing replacement of
> > certain values on fly.
> >
> >
> >
> > The causes are error data mismatch.
> >
> >
> >
> > NOTE: I try sp_executeslq and this entire thing.
> >
> >
> >
> >
> >
> > ALTER PROCEDURE dbo.StoredProcedure1
> >
> > /*
> >
> > (
> >
> > @.parameter1 datatype = default value,
> >
> > @.parameter2 datatype OUTPUT
> >
> > )
> >
> > */
> >
> > AS
> >
> >
> >
> > set language us_English
> >
> > set nocount on
> >
> >
> >
> > declare @.BirthDatePos int
> >
> > declare @.colum sysname -- sysname is equal to nvarchar(128)
> >
> >
> >
> > /*
> >
> > * ----
> >
> > * When @.DateFrom and @.DateTo are datetime the SQL-Server
> >
> > * not run. I get the following error message:
> >
> > * Syntax error converting datetime from character string.
> >
> > * ----
> >
> > */
> >
> > declare @.DateFrom datetime
> >
> > declare @.DateTo datetime
> >
> >
> >
> > /*
> >
> > * ---
> >
> > * When @.DateFrom and @.DateTo are both characters the SQL-Server
> >
> > * run but not return anything.
> >
> > * ---
> >
> > */
> >
> > --declare @.DateFrom nvarchar(8)
> >
> > --declare @.DateTo nvarchar(8)
> >
> >
> >
> > /* Creating my Dynamic SQL: */
> >
> > set @.DateFrom = '20040505'
> >
> > set @.DateTo = '20040505'
> >
> > set @.BirthDatePos = 6
> >
> > set @.colum = col_name(object_id('Employees'),@.BirthDatePos)
> >
> >
> >
> > SELECT BirthDate
> >
> > FROM Employees
> >
> > WHERE (@.colum >= @.DateFrom) AND (@.colum <= @.DateTo)
> >
> > set nocount off
> >
> > RETURN
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:#plMbyVMEHA.624@.TK2MSFTNGP11.phx.gbl...
> > > I just posted a reply to this. No need to repost.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > >
> > >
> > > "Mario Reiley" <mreiley@.cantv.net> wrote in message
> > news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> > > > In several messages that I post early I need help for create Dynamic
SQL
> > or
> > > > that I trying to do.
> > > >
> > > > Here is an example of my store procedure
> > > >
> > > > ALTER PROCEDURE dbo.Test
> > > > /*
> > > > (
> > > > @.parameter1 datatype = default value,
> > > > @.parameter2 datatype OUTPUT
> > > > )
> > > > */
> > > > AS
> > > >
> > > > SET LANGUAGE us_english
> > > > SET NOCOUNT ON
> > > >
> > > > declare @.Colum sysname
> > > > declare @.MyDate nvarchar(10)
> > > >
> > > > set @.MyDate = '2004-05-05'
> > > > set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
> > > >
> > > > print
'---'
> > > > print 'The following expression no generate an error but not return
me'
> > > > print 'anything rows. ( Both are String )'
> > > > print
'---'
> > > >
> > > > -- Both are String
> > > > print 'Expression:'
> > > > print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
> > > > print 'Result:'
> > > > print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> > > >
> > > > print
'================================================================'
> > > >
> > > > print
> > > >
> >
'----'
> > > > print 'Thi following expression generate an error:(to have types
> > mismatch).'
> > > > print
> > > >
> >
'----'
> > > >
> > > > -- To have types mismatch.
> > > > print 'Expression:'
> > > > print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
> > > > print 'Result:'
> > > > print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> > > >
> > > > print
'================================================================'
> > > >
> > > > SET NOCOUNT OFF
> > > > RETURN
> > > >
> > > >
> > > > -- The out --
> > > >
> > > >
> > > > Running dbo."Test".
> > > >
> > > > ---
> > > > The following expression no generate an error but not return me
> > > > anything rows. ( Both are String )
> > > > ---
> > > > Expression:
> > > > @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> > > > Result:
> > > > FechaMie>=2004-05-05
> > > > ================================================================> > > >
> > > >
> > > >
> > >
> ----
> > > > Thi following expression generate an error:(to have types mismatch).
> > >
> ----
> > > > Expression:
> > > > @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> > > > Result:
> > > > Syntax error converting datetime from character string.
> > > > No rows affected.
> > > > (0 row(s) returned)
> > > > @.RETURN_VALUE => > > > Finished running dbo."Test".
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>