|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
dtproperties system table changed to user table in sysobjects?Hi Gurus,
I occasionally use some T-SQL to add a column or trigger to all usertables tables within a database. It has always worked before but on one database I am having a problem because the "dtproperties" system table appears to have been changed to a user table in the "sysobjects" table and is being returned in along with all the usertables by: select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 I can skip that table easily enough, but can anyone tell me why this may have changed and if I should change it back to a system table, if so how would I go about that? Regards, Paul dtproperties is created when you create a database diagram. It is marked as
a user table by default so I suggest you exclude it by name in your script. -- David Portas SQL Server MVP -- "Paul B" <paul.bunt***@archsoftnet.com> wrote in message I occasionally use some T-SQL to add a column or trigger to all usertables news:%234h2UcmxFHA.2728@TK2MSFTNGP14.phx.gbl... Hi Gurus, tables within a database. It has always worked before but on one database I am having a problem because the "dtproperties" system table appears to have been changed to a user table in the "sysobjects" table and is being returned in along with all the usertables by: select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 I can skip that table easily enough, but can anyone tell me why this may have changed and if I should change it back to a system table, if so how would I go about that? Regards, Paul .... or also use IsMsShipped:
select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 AND OBJECTPROPERTY(id, N'IsMSShipped') = 0 -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:2f2dnarVIKxH5aPenZ2dnUVZ8t2dnZ2d@giganews.com... > dtproperties is created when you create a database diagram. It is marked as a user table by > default so I suggest you exclude it by name in your script. > > -- > David Portas > SQL Server MVP > -- > > "Paul B" <paul.bunt***@archsoftnet.com> wrote in message > news:%234h2UcmxFHA.2728@TK2MSFTNGP14.phx.gbl... > Hi Gurus, > > I occasionally use some T-SQL to add a column or trigger to all usertables tables within a > database. > > It has always worked before but on one database I am having a problem because the "dtproperties" > system table appears to have been changed to a user table in the "sysobjects" table and is being > returned in along with all the usertables by: > select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 > > I can skip that table easily enough, but can anyone tell me why this may have changed and if I > should change it back to a system table, if so how would I go about that? > > Regards, > > Paul > > Thanks,
Was a bit puzzled as to the fact that it was shown as a system table by Enterprise Manager and returned as a user table by "where OBJECTPROPERTY(id, N'IsUserTable') = 1". David mentioned it is added when you create a database diagram, is this always the case?... the database I had the error did not have any diagrams and never has (it was only created a couple of days ago), and a duplicate database created at the same time returned "dtproperties" as a system table! I had included an if statment to exclude that table, will probably switch it to "OBJECTPROPERTY(id, N'IsMSShipped')" as suggested by Tibor... is there any other tables that could be both system and user at the same time that this problem may occur with? Regards, Paul Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:eW7LtgpxFHA.2540@TK2MSFTNGP09.phx.gbl... > ... or also use IsMsShipped: > > select [name] > from dbo.sysobjects > where OBJECTPROPERTY(id, N'IsUserTable') = 1 > AND OBJECTPROPERTY(id, N'IsMSShipped') = 0 > > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message > news:2f2dnarVIKxH5aPenZ2dnUVZ8t2dnZ2d@giganews.com... >> dtproperties is created when you create a database diagram. It is marked >> as a user table by default so I suggest you exclude it by name in your >> script. >> >> -- >> David Portas >> SQL Server MVP >> -- >> >> "Paul B" <paul.bunt***@archsoftnet.com> wrote in message >> news:%234h2UcmxFHA.2728@TK2MSFTNGP14.phx.gbl... >> Hi Gurus, >> >> I occasionally use some T-SQL to add a column or trigger to all >> usertables tables within a database. >> >> It has always worked before but on one database I am having a problem >> because the "dtproperties" system table appears to have been changed to a >> user table in the "sysobjects" table and is being returned in along with >> all the usertables by: >> select [name] from dbo.sysobjects where OBJECTPROPERTY(id, >> N'IsUserTable') = 1 >> >> I can skip that table easily enough, but can anyone tell me why this may >> have changed and if I should change it back to a system table, if so how >> would I go about that? >> >> Regards, >> >> Paul >> >> > EM is hard-wired to show dtproperties as a system table... The table might be created by other EM
GUI elements, like the design table dialog etc. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Paul B" <paul.no-email-please.bunt***@archsoftnet.com> wrote in message news:Oduh6AsxFHA.2848@TK2MSFTNGP15.phx.gbl... > Thanks, > > Was a bit puzzled as to the fact that it was shown as a system table by Enterprise Manager and > returned as a user table by "where OBJECTPROPERTY(id, N'IsUserTable') = 1". > > David mentioned it is added when you create a database diagram, is this always the case?... the > database I had the error did not have any diagrams and never has (it was only created a couple of > days ago), and a duplicate database created at the same time returned "dtproperties" as a system > table! > > I had included an if statment to exclude that table, will probably switch it to > "OBJECTPROPERTY(id, N'IsMSShipped')" as suggested by Tibor... is there any other tables that could > be both system and user at the same time that this problem may occur with? > > Regards, > > Paul > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message > news:eW7LtgpxFHA.2540@TK2MSFTNGP09.phx.gbl... >> ... or also use IsMsShipped: >> >> select [name] >> from dbo.sysobjects >> where OBJECTPROPERTY(id, N'IsUserTable') = 1 >> AND OBJECTPROPERTY(id, N'IsMSShipped') = 0 >> >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message >> news:2f2dnarVIKxH5aPenZ2dnUVZ8t2dnZ2d@giganews.com... >>> dtproperties is created when you create a database diagram. It is marked as a user table by >>> default so I suggest you exclude it by name in your script. >>> >>> -- >>> David Portas >>> SQL Server MVP >>> -- >>> >>> "Paul B" <paul.bunt***@archsoftnet.com> wrote in message >>> news:%234h2UcmxFHA.2728@TK2MSFTNGP14.phx.gbl... >>> Hi Gurus, >>> >>> I occasionally use some T-SQL to add a column or trigger to all usertables tables within a >>> database. >>> >>> It has always worked before but on one database I am having a problem because the "dtproperties" >>> system table appears to have been changed to a user table in the "sysobjects" table and is being >>> returned in along with all the usertables by: >>> select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 >>> >>> I can skip that table easily enough, but can anyone tell me why this may have changed and if I >>> should change it back to a system table, if so how would I go about that? >>> >>> Regards, >>> >>> Paul >>> >>> >> > >  
Other interesting topics
|
|||||||||||||||||||||||
