Hi Gurus,
I occasionally use some T-SQL to add a column or trigger to all usertables t
ables within a database.
It has always worked before but on one database I am having a problem becaus
e the "dtproperties" system table appears to have been changed to a user tab
le in the "sysobjects" table and is being returned in along with all the use
rtables 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 ha
ve changed and if I should change it back to a system table, if so how would
I go about that?
Regards,
Pauldtproperties 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.bunting@.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|||... 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_dportas@.acm.org> wrote in message
news:2f2dnarVIKxH5aPenZ2dnUVZ8t2dnZ2d@.gi
ganews.com...
> dtproperties is created when you create a database diagram. It is marked a
s a user table by
> default so I suggest you exclude it by name in your script.
> --
> David Portas
> SQL Server MVP
> --
> "Paul B" <paul.bunting@.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 beca
use the "dtproperties"
> system table appears to have been changed to a user table in the "sysobjec
ts" 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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.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_dportas@.acm.org> wrote in message
> news:2f2dnarVIKxH5aPenZ2dnUVZ8t2dnZ2d@.gi
ganews.com...
>|||EM is hard-wired to show dtproperties as a system table... The table might b
e created by other EM
GUI elements, like the design table dialog etc.
Tibor 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.bunting@.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 En
terprise 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 al
ways the case?... the
> database I had the error did not have any diagrams and never has (it was o
nly created a couple of
> days ago), and a duplicate database created at the same time returned "dtp
roperties" 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_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eW7LtgpxFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
