BISQL # 63 – How to get list of Object with its Owner information and Object type in SQL Server ?

Hi folks ‘

As we always need to modify always user and its security information very frequently.

So while changing or updating any information related to object and its owner one should analyze current details as well.

Or else some time your manager/customer may ask you a list of object with its owner and object type

So following script will provide you all details.

This script give three column details

1.Object Name : This column will contains object names for database.

2.Object Owner : This will represent owner of the object.

3.Object Type : This column represents Type of object .

Script :

SELECT
   NAME               AS 'Object Name' ,
   USER_NAME(uid)     AS 'Object Owner',
   CASE (xtype)
      WHEN 'AF' THEN 'Aggregate function (CLR)'
      WHEN 'C'  THEN 'CHECK constraint' 
      WHEN 'D'  THEN 'DEFAULT (constraint or stand-alone)'
      WHEN 'F'  THEN 'FOREIGN KEY constraint'
      WHEN 'PK' THEN 'PRIMARY KEY constraint'
      WHEN 'P'  THEN 'SQL stored procedure'
      WHEN 'PC' THEN 'Assembly (CLR) stored procedure'
      WHEN 'FN' THEN 'SQL scalar function'
      WHEN 'FS' THEN 'Assembly (CLR) scalar function'
      WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
      WHEN 'R'  THEN 'Rule (old-style, stand-alone)'
      WHEN 'RF' THEN 'Replication-filter-procedure'
      WHEN 'S'  THEN 'System base table'
      WHEN 'SN' THEN 'Synonym'
      WHEN 'SQ' THEN 'Service queue'
      WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
      WHEN 'TR' THEN 'SQL DML trigger '
      WHEN 'IF' THEN 'SQL inline table-valued function'
      WHEN 'TF' THEN 'SQL table-valued-function'
      WHEN 'U'  THEN 'Table (user-defined)'
      WHEN 'UQ' THEN 'UNIQUE constraint'
      WHEN 'V'  THEN 'View'
      WHEN 'X'  THEN 'Extended stored procedure'
      WHEN 'IT' THEN 'Internal table'
   END            AS 'Object Type'
 FROM sysobjects
 WHERE USER_NAME(uid) NOT IN ('sys', 'INFORMATION_SCHEMA')
 ORDER BY 'Object Type', 'Object Name', 'Object Owner'

Output :

Hope this query is useful for you !!

Thanks for visiting my blog !!

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource Website