In SQL Server 2000, is there a sysobjects query that will retrieve user views and not system views?

Assuming such a query exists, I would greatly appreciate the help.

I'm trying to develop a permissions script that will grant "select" and "references" permissions on the user tables and views in a database. My hope is that executing the "grant" commands on each element in such a set will make it easier to keep permissions current when new tables and views are added to the database.

From stackoverflow Scott A. Lawrence

• select * from information_schema.tables
  where table_type = 'view'
  

  From Jas

• select * from information_schema.tables
  WHERE OBJECTPROPERTY(OBJECT_ID(table_name),'IsMSShipped') =0
  

  Will exclude dt_properties and system tables

  add

  where table_type = 'view'
  

  if you just want the view

  From SQLMenace

• SELECT
      *
  FROM
      sysobjects
  WHERE
      xtype = 'V' AND
      type = 'V' AND
      category = 0
  

  Here is a list of the possible values for xtype:

  • C = CHECK constraint
  • D = Default or DEFAULT constraint
  • F = FOREIGN KEY constraint
  • L = Log
  • P = Stored procedure
  • PK = PRIMARY KEY constraint (type is K)
  • RF = Replication filter stored procedure
  • S = System table
  • TR = Trigger
  • U = User table
  • UQ = UNIQUE constraint (type is K)
  • V = View
  • X = Extended stored procedure

  Here are the possible values for type:

  • C = CHECK constraint
  • D = Default or DEFAULT constraint
  • F = FOREIGN KEY constraint
  • FN = Scalar function
  • IF = Inlined table-function
  • K = PRIMARY KEY or UNIQUE constraint
  • L = Log
  • P = Stored procedure
  • R = Rule
  • RF = Replication filter stored procedure
  • S = System table
  • TF = Table function
  • TR = Trigger
  • U = User table
  • V = View
  • X = Extended stored procedure

  Finally, the category field looks like it groups based on different types of objects. After analyzing the return resultset, the system views look to have a category = 2, whereas all of the user views have a category = 0. Hope this helps.

  For more information, visit http://msdn.microsoft.com/en-us/library/aa260447(SQL.80).aspx

  From KG