top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to Get all objects in a database using PL-SQL in SQL server 2008?

0 votes
408 views
How to Get all objects in a database using PL-SQL in SQL server 2008?
posted Mar 19, 2016 by Sathyasree

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

In SQL server 2008 we can retrieve all objects in a database with a very simple query

Query:

select

name as 'Name',

object_id as 'Object ID',

principal_id as 'Principal ID',

schema_id as 'Schema ID',

parent_object_id as 'Object ID',

type as 'Type',

type_desc as 'Type Description',

create_date as 'Create date',

modify_date as 'Modify date',

is_ms_shipped as 'Shipped',

is_published as 'Published'

from sys.objects

Results:

sysrowsetcolumns      4              NULL        4              0              S              SYSTEM_TABLE        1              0

sysallocunits              7              NULL        4              0              S              SYSTEM_TABLE        1              0

syshobtcolumns         13             NULL        4              0              S              SYSTEM_TABLE        1              0

Area          **********               NULL        1              0              U              USER_TABLE            0              0

Settings     **********               NULL        1              0              U              USER_TABLE            0              0

Roles        **********               NULL        1              0              U              USER_TABLE            0              0

Customers **********               NULL        1              0              U              USER_TABLE            0              0

Using this functionality we can easily get all objects created by any user in any database.

answer Mar 19, 2016 by Shivaranjini
...