Distinguish User Objects and System objects

How to differentiate between user objects(table,view...) and system objects. Where is this information stored in SQL Server 7.0?
Thanks in advance,
Arr S
How to differentiate where? Are you running a query? What is it? WHat
version of SQL Server are you running?
Typically you can add one of the following to limit the resultset to only
user-defined objects, depending on how you are querying for system objects:
WHERE OBJECTPROPERTY(id, 'isMSShipped') = 0
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'isMSShipped')=0
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'isMSShipped')=0
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
> objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S
|||You could try looking in the sysobjects table of the respective databases,
and use the Type column to distinguish between system and user created
objects. Look up the help file for details.
Regards
Ray Mond
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S
|||Hi,
The information about System and UserDefined Objects is stored in
sysobjects database... there are multiple ways for know userdefined
objects...
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 1
--this statment gives all user defined tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 0
--this statment gives all system tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsForeignKey')
= 1
--this statment gives all forien key.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsPrimaryKey')
= 1
--this statment gives all primary key.
etc...
Regards-Hari Sharma
"Ray Mond" <yeohray@.hotmail.com> wrote in message news:<#Kbz7wSFEHA.3132@.TK2MSFTNGP12.phx.gbl>...
> You could try looking in the sysobjects table of the respective databases,
> and use the Type column to distinguish between system and user created
> objects. Look up the help file for details.
> --
> Regards
> Ray Mond
> "Arr S" <anonymous@.discussions.microsoft.com> wrote in message
> news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> objects. Where is this information stored in SQL Server 7.0?

Distinguish User Objects and System objects

How to differentiate between user objects(table,view...) and system objects.
Where is this information stored in SQL Server 7.0?
Thanks in advance,
Arr SHow to differentiate where? Are you running a query? What is it? WHat
version of SQL Server are you running?
Typically you can add one of the following to limit the resultset to only
user-defined objects, depending on how you are querying for system objects:
WHERE OBJECTPROPERTY(id, 'isMSShipped') = 0
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'isMSShipped')=0
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'isMSShipped')=0
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
> objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S|||You could try looking in the sysobjects table of the respective databases,
and use the Type column to distinguish between system and user created
objects. Look up the help file for details.
Regards
Ray Mond
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S|||Hi,
The information about System and UserDefined Objects is stored in
sysobjects database... there are multiple ways for know userdefined
objects...
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 1
--this statment gives all user defined tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 0
--this statment gives all system tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsForeignKey')
= 1
--this statment gives all forien key.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsPrimaryKey')
= 1
--this statment gives all primary key.
etc...
Regards-Hari Sharma
"Ray Mond" <yeohray@.hotmail.com> wrote in message news:<#Kbz7wSFEHA.3132@.TK2MSFTNGP12.phx.g
bl>...
> You could try looking in the sysobjects table of the respective databases,
> and use the Type column to distinguish between system and user created
> objects. Look up the help file for details.
> --
> Regards
> Ray Mond
> "Arr S" <anonymous@.discussions.microsoft.com> wrote in message
> news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> objects. Where is this information stored in SQL Server 7.0?

Distinguish SQLServer and MSDE instances

How can I tell which of the SQLServers listed on the network are
specifically MSDE.
I want to make sure that I have licenses for all the full versions of
SQLServer that are running.
Thanks.
Alan
Issue one of the commands on the servers you want to find that out:
Select @.@.Version
--OR
EXEC master..xp_msver
HTH, Jens Suessmeyer
|||Another command would be to SELECT SERVERPROPERTY('Edition');
http://msdn.microsoft.com/library/?u...asp?frame=true
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Jens & Alan,
Thanks very much.
Regards,
Alan
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:Ol87ezb6FHA.3276@.TK2MSFTNGP10.phx.gbl...
> How can I tell which of the SQLServers listed on the network are
> specifically MSDE.
> I want to make sure that I have licenses for all the full versions of
> SQLServer that are running.
> Thanks.
> Alan
>
>

Distinguish changes by replication and changes by user

Hello,
Merge Replication, SQL Server 2005
I have got a table with an update trigger that is supposed to fire when a
user updates a field.
When replicating this database, the trigger fires when a user updates a
field *and* when the update is performed by the replication process.
How can I avoid the latter? Is there a TSQL-function to distinguish the two?
Thanks for your effort, Wolfgang
Another way is to hack into the sessionproperty
ie if ('replication_agent') <> 1 then its a user action.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wolfgang" <Wolfgang@.discussions.microsoft.com> wrote in message
news:168538E7-89A2-45F3-ABD6-566F2C4B3760@.microsoft.com...
> Hello,
> Merge Replication, SQL Server 2005
> I have got a table with an update trigger that is supposed to fire when a
> user updates a field.
> When replicating this database, the trigger fires when a user updates a
> field *and* when the update is performed by the replication process.
> How can I avoid the latter? Is there a TSQL-function to distinguish the
> two?
> Thanks for your effort, Wolfgang
sql

distinguish between the Publisher and the Subscribers

I have a database on SQL Sever 2005 SP1 against which a Publication has been defined and to which many servers (both Workgroup and Express editions - SP1) Subscribe to.

I would like to be able to distinguish between the Publisher and the Subscribers programmatically via T-SQL.

From reading BoL and various forums it appears that the IsPublished, IsMergePublished and IsSubscribed options of the DatabasePropertyEx function should give me this information.

However within all our tested environments, whilst the IsMergePublished option returns expected values. IsPublished and IsSubscribed both return 0 on all servers (the Publisher and Subscribers).

Is this a know issue and how can I rectify the problem or alternatively does anyone know of another method to distinguishing between the Publishers and Subscribers.

Thanks

Hi Steve,

From BOL, IsPublished means "The tables of the database can be published for snapshot or transactional replication, if replication is installed.". So if your publication DB has merge publication, this value should be 0.

For IsSubscribed, looks like it is a documentation bug. I will open a bug for it.

Peng

|||Query distribution.dbo.MSmerge_subscriptions to find the subscribers. and distribution.dbo.MSpublications to find the publishers.