{"id":2784,"date":"2014-03-07T15:51:03","date_gmt":"2014-03-07T19:51:03","guid":{"rendered":"http:\/\/www.acarlstein.com\/?p=2784"},"modified":"2014-03-07T15:59:45","modified_gmt":"2014-03-07T19:59:45","slug":"mssql-cheat-sheet","status":"publish","type":"post","link":"http:\/\/blog.acarlstein.com\/?p=2784","title":{"rendered":"MSSQL Basic Cheat Sheet"},"content":{"rendered":"\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

Description<\/h2>\n<\/td>\n

\n

Example<\/h2>\n<\/td>\n<\/tr>\n

Version<\/td>\nSELECT @@version<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/tr>\n
List of Users<\/td>\nSELECT name FROM master..syslogins<\/td>\n<\/tr>\n
Current User<\/td>\nSELECT 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;<\/td>\n<\/tr>\n
Privileges<\/td>\nSELECT is_srvrolemember(‘sysadmin’);
\nSELECT is_srvrolemember(‘securityadmin’);
\nSELECT is_srvrolemember(‘serveradmin’);
\nSELECT is_srvrolemember(‘setupadmin’);
\nSELECT is_srvrolemember(‘diskadmin’);
\nSELECT is_srvrolemember(‘bulkadmin’);
\nSELECT is_srvrolemember(‘dbcreator’);———————————————————-SELECT name FROM master..syslogins WHERE sysadmin = 1;
\nSELECT name FROM master..syslogins WHERE securityadmin = 1;
\nSELECT name FROM master..syslogins WHERE serveradmin = 1;
\nSELECT name FROM master..syslogins WHERE serveradmin = 1; — May return empty
\nSELECT name FROM master..syslogins WHERE setupadmin = 1; — May return empty
\nSELECT name FROM master..syslogins WHERE diskadmin = 1; — May return empty
\nSELECT name FROM master..syslogins WHERE bulkadmin = 1;
\nSELECT name FROM master..syslogins WHERE dbcreator = 1;
\nSELECT name FROM master..syslogins WHERE hasaccess = 1;
\nSELECT name FROM master..syslogins WHERE denylogin = 0;
\nSELECT name FROM master..syslogins WHERE isntname = 0;
\nSELECT name FROM master..syslogins WHERE isntgroup = 0;<\/td>\n<\/tr>\n
List All Databases<\/td>\n\u00a0SELECT name FROM master..sysdatabases;<\/td>\n<\/tr>\n
Database by Id<\/td>\n\u00a0SELECT DB_NAME(ID); — Where ID is 0, 1, 2, …, N<\/td>\n<\/tr>\n
Current Database<\/td>\n\u00a0SELECT DB_NAME();<\/td>\n<\/tr>\n
List Columns<\/td>\nSELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name=’TABLE_NAME’); — Where TABLE_NAME is the table name on the current database<\/td>\n<\/tr>\n
List Tables<\/td>\nSELECT name FROM DATABASE_NAME..sysobjects WHERE xtype = ‘U’; — Where DATABASE_NAME is the database which you wish to list the tables<\/td>\n<\/tr>\n
List Views<\/td>\nSELECT name FROM DATABASE_NAME..sysobjects WHERE xtype = ‘V’; — Where DATABASE_NAME is the database which you wish to list the views<\/td>\n<\/tr>\n
Hostname<\/td>\nSELECT HOST_NAME();<\/td>\n<\/tr>\n
Information and Location of DB file<\/td>\nEXEC sp_helpdb DATABASE_NAME; — Where DATABASE_NAME is the name of the database<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n