Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 819
Scott Lawrence Avatar asked Aug 28 '08 19:08

Scott Lawrence


2 Answers

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

like image 70
SQLMenace Avatar answered Nov 06 '22 09:11

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

like image 30
karlgrz Avatar answered Nov 06 '22 10:11

karlgrz