Posted by
Description |
Example |
| Version | SELECT @@version |
| List of Users | SELECT name FROM master..syslogins |
| Current User | SELECT user; — Returns user such as “dbo”SELECT user_name(); — Returns user such as “dbo”SELECT system_user; — Returns [DOMAIN]\[USERNAME]SELECT loginame FROM master.sysprocesses WHERE spid = @@SPID; |
| Privileges | SELECT is_srvrolemember(‘sysadmin’); SELECT is_srvrolemember(‘securityadmin’); SELECT is_srvrolemember(‘serveradmin’); SELECT is_srvrolemember(‘setupadmin’); SELECT is_srvrolemember(‘diskadmin’); SELECT is_srvrolemember(‘bulkadmin’); SELECT is_srvrolemember(‘dbcreator’);———————————————————-SELECT name FROM master..syslogins WHERE sysadmin = 1; SELECT name FROM master..syslogins WHERE securityadmin = 1; SELECT name FROM master..syslogins WHERE serveradmin = 1; SELECT name FROM master..syslogins WHERE serveradmin = 1; — May return empty SELECT name FROM master..syslogins WHERE setupadmin = 1; — May return empty SELECT name FROM master..syslogins WHERE diskadmin = 1; — May return empty SELECT name FROM master..syslogins WHERE bulkadmin = 1; SELECT name FROM master..syslogins WHERE dbcreator = 1; SELECT name FROM master..syslogins WHERE hasaccess = 1; SELECT name FROM master..syslogins WHERE denylogin = 0; SELECT name FROM master..syslogins WHERE isntname = 0; SELECT name FROM master..syslogins WHERE isntgroup = 0; |
| List All Databases | SELECT name FROM master..sysdatabases; |
| Database by Id | SELECT DB_NAME(ID); — Where ID is 0, 1, 2, …, N |
| Current Database | SELECT DB_NAME(); |
| List Columns | SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name=’TABLE_NAME’); — Where TABLE_NAME is the table name on the current database |
| List Tables | SELECT name FROM DATABASE_NAME..sysobjects WHERE xtype = ‘U’; — Where DATABASE_NAME is the database which you wish to list the tables |
| List Views | SELECT name FROM DATABASE_NAME..sysobjects WHERE xtype = ‘V’; — Where DATABASE_NAME is the database which you wish to list the views |
| Hostname | SELECT HOST_NAME(); |
| Information and Location of DB file | EXEC sp_helpdb DATABASE_NAME; — Where DATABASE_NAME is the name of the database |
© 2014, Alejandro G. Carlstein Ramos Mejia. All rights reserved.
