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
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.
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()