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

Question

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.

Answer 1

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

Answer 2

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