Search code examples
t-sqlsql-server-2005sql-drop

What is the "god" sys table?

IF  EXISTS (SELECT * FROM sys.all_objects WHERE name = N'SOMELOGIN')
DROP USER [SOMELOGIN]
GO

Does not work because USER SOMELOGIN does not live in sys.all_objects.

Is there a global "god" table I can look in to see if something exists. (i.e. dropping it when it doesn't exist doesn't throw an error)

Alternatively is there an online resource for finding out where certain types of objects live?

I need to drop the following

  • USER
  • ASYMMETRIC KEY
  • LOGIN
  • CERTIFICATE
Solution

  • sys.objects is used for objects in a database, such as tables, stored procedures, views etc.

    I think you need the following tables:

    SELECT * FROM sys.sysusers
WHERE [name] = 'someUser'

SELECT * FROM sys.asymmetric_keys
WHERE [name] = 'someKey'

SELECT * FROM sys.certificates
WHERE [name] = 'someCertificate'

SELECT * FROM sys.syslogins
WHERE [name] = 'someLogin'

    EDIT

    The nearest thing I can find for detailing the system views is this. It splits them out by type of view. For instance, drilling down to Catalog Views > Security Catalog Views will give you the views for security related views e.g. sys.asymmetric_keys

    I'm not aware of anything that will give you a Logins > sys.syslogins type of mapping.