Jazz Jazz - 8 days ago 6
SQL Question

SQL: Replacing code with sysobjects to one with sys.objects

I've been playing around with some queries involving Metadata (hopefully, I'm using the correct terminology) and one of the thing I did was to return all the tables created by the current user, using the database.

This is the script:

SELECT so.name as [Table], su.name as [User]
FROM sysobjects so
INNER JOIN sysusers su
ON so.uid = su.uid
WHERE xtype = 'U' -- user-defined table
AND su.name=USER_NAME()


Then I read that
sysobjects
is in the system only for backward compatibility with SQL Server 2000, and one must use
sys.objects
instead. Well, I tried to change the code, but
sys.objects
doesn't has a column with information related to the user (
sysobjects
has the column
uid
) who created the listed objects.

How can I replace
sysobjects
above by
sys.objects
?

Answer

Use the sys.objects table. The principal_id column contains the value you want:

principal_id - ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.

Source: sys.objects documentation.

Try this query:

SELECT   t.name AS Table_Name
        ,t.principal_id AS Table_Owner_User_Id
        ,s.schema_id
        ,s.name AS Schema_Name
        ,s.principal_id AS Schema_Owner_User_Id
FROM sys.objects t
    INNER JOIN sys.schemas s
        ON t.schema_id = s.schema_id
WHERE t.type = 'U'
AND COALESCE(t.principal_id, s.principal_id) = USER_ID()
-- Or, if you must use USER_NAME(), then use the following instead:
--AND USER_NAME(COALESCE(t.principal_id, s.principal_id)) = USER_NAME()