Francisco H Tapia
my.lists at verizon.net
Wed Mar 12 17:56:59 CST 2003
Did not know that... because if I run it as sp_fkeys @fktable_name = 'tbl_ccStatus' It is using the @fktable_name variable. it is implying by definition (@fktable) that only the Fk are to be brought over and that the table defined is the FK table, whereas if I run it as sp_fkeys tbl_ccStatus I then get nothing back... just my 2 cents. I like the shorter syntax... and I'm going to keep working with it to maximize it's use but I haven't chewed through that chapter in BOL yet. -Francisco http://rcm.netfirms.com On Wednesday, March 12, 2003 2:46 PM [GMT-8], Djabarov, Robert <Robert.Djabarov at usaa.com> wrote: : Sp_fkeys authors : will return PK and FK information on authors : : Robert Djabarov : : -----Original Message----- : From: Francisco H Tapia [mailto:my.lists at verizon.net] : Sent: Wednesday, March 12, 2003 4:42 PM : To: dba-sqlserver at databaseadvisors.com : Subject: Re: [dba-SQLServer]Deleting record (Column Reference : Constraint) : : is this the right syntax?, or should I use the EXEC before it? : : sp_fkeys @fktable_name = 'tbl_ccStatus' : : Simple but I still like my script better as I don't have to give it an : @pktable_name, just a single table name and it goes out and finds all : related FK's for it.. : : : : -Francisco : http://rcm.netfirms.com : : On Wednesday, March 12, 2003 2:21 PM [GMT-8], : Djabarov, Robert <Robert.Djabarov at usaa.com> wrote: : :: Sp_fkeys is available for 6.5, 7.0, and 2K and works in all three :: versions. :: :: Robert Djabarov :: Senior SQL Server DBA :: USAA IT/DBMS :: ? (210) 913-3148 - phone :: ? (210) 753-3148 - pager :: :: -----Original Message----- :: From: Francisco H Tapia [mailto:my.lists at verizon.net] :: Sent: Wednesday, March 12, 2003 4:04 PM :: To: dba-sqlserver at databaseadvisors.com :: Subject: Re: [dba-SQLServer]Deleting record (Column Reference :: Constraint) :: :: I don't get back any data with this sp....(on SQL7) but I do get data :: for the script I'm using.. and it works on both 7 and 2k :S :: :: -Francisco :: http://rcm.netfirms.com :: :: On Wednesday, March 12, 2003 1:35 PM [GMT-8], :: Djabarov, Robert <Robert.Djabarov at usaa.com> wrote: :: ::: sp_fkeys <table_name> ::: ::: Robert Djabarov ::: Senior SQL Server DBA ::: USAA IT/DBMS ::: ? (210) 913-3148 - phone ::: ? (210) 753-3148 - pager ::: ::: -----Original Message----- ::: From: Francisco H Tapia [mailto:my.lists at verizon.net] ::: Sent: Wednesday, March 12, 2003 3:18 PM ::: To: dba-sqlserver at databaseadvisors.com ::: Subject: Re: [dba-SQLServer]Deleting record (Column Reference ::: Constraint) ::: ::: In QA, I use this script to find constraints on my tables ::: ::: USE YOURDBNAMEHERE ::: Declare @Var as varchar(100) ::: SET @Var = 'THE_TABLE_YOUR_TRYING_TO_DELETE_FROM_NAME_HERE' ::: ::: SELECT constraint_name, ::: column_name, ::: Table_Name, ::: ordinal_position ::: FROM information_schema.key_column_usage ::: WHERE constraint_catalog = db_name() ::: AND (CONSTRAINT_NAME Like '%' + @Var OR CONSTRAINT_NAME Like '%' ::: + @Var +'%') ::: AND LEFT(CONSTRAINT_NAME,2) = 'FK' ::: ORDER BY constraint_name, ordinal_position ::: ::: -Francisco ::: http://rcm.netfirms.com ::: ::: On Wednesday, March 12, 2003 12:30 PM [GMT-8], ::: David Emerson <davide at dalyn.co.nz> wrote: ::: :::: Thanks for your reply. Where do I find the definition reference? :::: :::: At 12/03/2003, you wrote: ::::: The only time I've seen this type of error is when a related ::::: record is going to be deleted. What is the defenition reference ::::: for tblCustInvoiceMeter_FK00? ::::: ::::: -Francisco ::::: http://rcm.netfirms.com ::::: ::::: David Emerson wrote: :::::: AXP/SQL2K :::::: :::::: I have a subform which causes an error message when I delete a :::::: record from it - :::::: :::::: DELETE statement conflicted with COLUMN REFERENCE constraint :::::: 'tblCustInvoiceMeter_FK00'. :::::: :::::: BOL doesn't seem to help. :::::: :::::: There are no related records to the one I am deleting. Can :::::: anyone enlighten me on the problem? :::::: :::::: :::::: Regards ::::: ::::: ::::: _______________________________________________ ::::: dba-SQLServer mailing list ::::: dba-SQLServer at databaseadvisors.com ::::: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver ::::: http://www.databaseadvisors.com :::: :::: Regards :::: :::: David Emerson :::: DALYN Software Ltd :::: 25b Cunliffe St, Johnsonville :::: Wellington, New Zealand :::: Ph/Fax (877) 456-1205 :::: :::: _______________________________________________ :::: dba-SQLServer mailing list :::: dba-SQLServer at databaseadvisors.com :::: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver :::: http://www.databaseadvisors.com ::: ::: ::: _______________________________________________ ::: dba-SQLServer mailing list ::: dba-SQLServer at databaseadvisors.com ::: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver ::: http://www.databaseadvisors.com ::: ::: ::: ::: _______________________________________________ ::: dba-SQLServer mailing list ::: dba-SQLServer at databaseadvisors.com ::: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver ::: http://www.databaseadvisors.com :: :: :: _______________________________________________ :: dba-SQLServer mailing list :: dba-SQLServer at databaseadvisors.com :: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver :: http://www.databaseadvisors.com :: :: :: :: _______________________________________________ :: dba-SQLServer mailing list :: dba-SQLServer at databaseadvisors.com :: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver :: http://www.databaseadvisors.com : : : _______________________________________________ : dba-SQLServer mailing list : dba-SQLServer at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver : http://www.databaseadvisors.com : : : : _______________________________________________ : dba-SQLServer mailing list : dba-SQLServer at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver : http://www.databaseadvisors.com