Find list of Reserved Keywords used as column names or table names

It is always good not to use any reserve keywords as any of the user objects. 
If you suspect that your database has reserved keywords as column names or table names then the below query will help you find out.

Use <DatabaseName> 
GO 
DECLARE @ReservedKeyWords Table (KeyWords VARCHAR(100))
INSERT INTO @ReservedKeyWords
SELECT 'ADD' UNION
SELECT 'EXTERNAL' UNION
SELECT 'PROCEDURE' UNION
SELECT 'ALL' UNION
SELECT 'FETCH' UNION
SELECT 'PUBLIC' UNION
SELECT 'ALTER' UNION
SELECT 'FILE' UNION
SELECT 'RAISERROR' UNION
SELECT 'AND' UNION
SELECT 'FILLFACTOR' UNION
SELECT 'READ' UNION
SELECT 'ANY' UNION
SELECT 'FOR' UNION
SELECT 'READTEXT' UNION
SELECT 'AS' UNION
SELECT 'FOREIGN' UNION
SELECT 'RECONFIGURE' UNION
SELECT 'ASC' UNION
SELECT 'FREETEXT' UNION
SELECT 'REFERENCES' UNION
SELECT 'AUTHORIZATION' UNION
SELECT 'FREETEXTTABLE' UNION
SELECT 'REPLICATION' UNION
SELECT 'BACKUP' UNION
SELECT 'FROM' UNION
SELECT 'RESTORE' UNION
SELECT 'BEGIN' UNION
SELECT 'FULL' UNION
SELECT 'RESTRICT' UNION
SELECT 'BETWEEN' UNION
SELECT 'FUNCTION' UNION
SELECT 'RETURN' UNION
SELECT 'BREAK' UNION
SELECT 'GOTO' UNION
SELECT 'REVERT' UNION
SELECT 'BROWSE' UNION
SELECT 'GRANT' UNION
SELECT 'REVOKE' UNION
SELECT 'BULK' UNION
SELECT 'GROUP' UNION
SELECT 'RIGHT' UNION
SELECT 'BY' UNION
SELECT 'HAVING' UNION
SELECT 'ROLLBACK' UNION
SELECT 'CASCADE' UNION
SELECT 'HOLDLOCK' UNION
SELECT 'ROWCOUNT' UNION
SELECT 'CASE' UNION
SELECT 'IDENTITY' UNION
SELECT 'ROWGUIDCOL' UNION
SELECT 'CHECK' UNION
SELECT 'IDENTITY_INSERT' UNION
SELECT 'RULE' UNION
SELECT 'CHECKPOINT' UNION
SELECT 'IDENTITYCOL' UNION
SELECT 'SAVE' UNION
SELECT 'CLOSE' UNION
SELECT 'IF' UNION
SELECT 'SCHEMA' UNION
SELECT 'CLUSTERED' UNION
SELECT 'IN' UNION
SELECT 'SECURITYAUDIT' UNION
SELECT 'COALESCE' UNION
SELECT 'INDEX' UNION
SELECT 'SELECT' UNION
SELECT 'COLLATE' UNION
SELECT 'INNER' UNION
SELECT 'SEMANTICKEYPHRASETABLE' UNION
SELECT 'COLUMN' UNION
SELECT 'INSERT' UNION
SELECT 'SEMANTICSIMILARITYDETAILSTABLE' UNION
SELECT 'COMMIT' UNION
SELECT 'INTERSECT' UNION
SELECT 'SEMANTICSIMILARITYTABLE' UNION
SELECT 'COMPUTE' UNION
SELECT 'INTO' UNION
SELECT 'SESSION_USER' UNION
SELECT 'CONSTRAINT' UNION
SELECT 'IS' UNION
SELECT 'SET' UNION
SELECT 'CONTAINS' UNION
SELECT 'JOIN' UNION
SELECT 'SETUSER' UNION
SELECT 'CONTAINSTABLE' UNION
SELECT 'KEY' UNION
SELECT 'SHUTDOWN' UNION
SELECT 'CONTINUE' UNION
SELECT 'KILL' UNION
SELECT 'SOME' UNION
SELECT 'CONVERT' UNION
SELECT 'LEFT' UNION
SELECT 'STATISTICS' UNION
SELECT 'CREATE' UNION
SELECT 'LIKE' UNION
SELECT 'SYSTEM_USER' UNION
SELECT 'CROSS' UNION
SELECT 'LINENO' UNION
SELECT 'TABLE' UNION
SELECT 'CURRENT' UNION
SELECT 'LOAD' UNION
SELECT 'TABLESAMPLE' UNION
SELECT 'CURRENT_DATE' UNION
SELECT 'MERGE' UNION
SELECT 'TEXTSIZE' UNION
SELECT 'CURRENT_TIME' UNION
SELECT 'NATIONAL' UNION
SELECT 'THEN' UNION
SELECT 'CURRENT_TIMESTAMP' UNION
SELECT 'NOCHECK' UNION
SELECT 'TO' UNION
SELECT 'CURRENT_USER' UNION
SELECT 'NONCLUSTERED' UNION
SELECT 'TOP' UNION
SELECT 'CURSOR' UNION
SELECT 'NOT' UNION
SELECT 'TRAN' UNION
SELECT 'DATABASE' UNION
SELECT 'NULL' UNION
SELECT 'TRANSACTION' UNION
SELECT 'DBCC' UNION
SELECT 'NULLIF' UNION
SELECT 'TRIGGER' UNION
SELECT 'DEALLOCATE' UNION
SELECT 'OF' UNION
SELECT 'TRUNCATE' UNION
SELECT 'DECLARE' UNION
SELECT 'OFF' UNION
SELECT 'TRY_CONVERT' UNION
SELECT 'DEFAULT' UNION
SELECT 'OFFSETS' UNION
SELECT 'TSEQUAL' UNION
SELECT 'DELETE' UNION
SELECT 'ON' UNION
SELECT 'UNION' UNION
SELECT 'DENY' UNION
SELECT 'OPEN' UNION
SELECT 'UNIQUE' UNION
SELECT 'DESC' UNION
SELECT 'OPENDATASOURCE' UNION
SELECT 'UNPIVOT' UNION
SELECT 'DISK' UNION
SELECT 'OPENQUERY' UNION
SELECT 'UPDATE' UNION
SELECT 'DISTINCT' UNION
SELECT 'OPENROWSET' UNION
SELECT 'UPDATETEXT' UNION
SELECT 'DISTRIBUTED' UNION
SELECT 'OPENXML' UNION
SELECT 'USE' UNION
SELECT 'DOUBLE' UNION
SELECT 'OPTION' UNION
SELECT 'USER' UNION
SELECT 'DROP' UNION
SELECT 'OR' UNION
SELECT 'VALUES' UNION
SELECT 'DUMP' UNION
SELECT 'ORDER' UNION
SELECT 'VARYING' UNION
SELECT 'ELSE' UNION
SELECT 'OUTER' UNION
SELECT 'VIEW' UNION
SELECT 'END' UNION
SELECT 'OVER' UNION
SELECT 'WAITFOR' UNION
SELECT 'ERRLVL' UNION
SELECT 'PERCENT' UNION
SELECT 'WHEN' UNION
SELECT 'ESCAPE' UNION
SELECT 'PIVOT' UNION
SELECT 'WHERE' UNION
SELECT 'EXCEPT' UNION
SELECT 'PLAN' UNION
SELECT 'WHILE' UNION
SELECT 'EXEC' UNION
SELECT 'PRECISION' UNION
SELECT 'WITH' UNION
SELECT 'EXECUTE' UNION
SELECT 'PRIMARY' UNION
SELECT 'WITHIN GROUP' UNION
SELECT 'EXISTS' UNION
SELECT 'PRINT' UNION
SELECT 'WRITETEXT' UNION
SELECT 'EXIT' UNION
SELECT 'PROC'

SELECT OBJECT_NAME(object_id) AS TableName
         ,name AS ColumnName
         ,column_id AS ColumnID 
FROM sys.columns WHERE object_id in (SELECT object_id FROM sys.objects WHERE type_desc='USER_TABLE')
AND name IN (select KeyWords from @ReservedKeyWords) 

SELECT name as TableName from sys.objects  WHERE type_desc='USER_TABLE'
AND name IN (select KeyWords from @ReservedKeyWords)

SQL:When was a Database Deleted and by whom

SQL:When was a Database Deleted and by whom

Here is a T-SQL query which gives information on who and when was a database deleted.

SELECT name AS EventName, DatabaseName, StartTime, LoginName,EventSubClass,ObjectName

    FROM sys.traces T CROSS Apply 

 fn_trace_gettable(CASE WHEN CHARINDEX('_', [path]) <> 0

                           THEN SUBSTRING(PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'

                           ELSE [path]

                         END, max_files) TT

    JOIN sys.trace_events TE ON TT.EventClass = TE.trace_event_id

WHERE name like '%Deleted%' 

--and LoginName='DOMAIN\rmahali'

 AND ObjectName IS NULL AND EventSubClass = 1

--ORDER BY StartTime DESC

SQL TIPS: MSSQL DBA Check List 

08SEP

Under the covers of MSSQL are these roles: sysadmin, datareader, datawriter, security admin, backupadmin etc. In larger organizations there should be three different people with these jobs

1. sysadmin – all access vip, often has windows admin and disk subsystem access as well
2. dbo – vip but only for a specific databases and not system level
3. developer/user – read and/or write to specific objects

Only level 1 qualifies you as a dba. Some dbas don’t know how to function at level 2 and 3. This is ok but not advised. DBAs need some level of automation skills and should be able to relate to developers. They also need to understand why the database structure is the number reason for performance problems.

Regular MSSQL DBA task include:

1. making sure backups are happening
2. monitoring performance
3. monitoring disk space
4. making sure index maintenance is happening
5. setting up new instances
6. consolidating instances

Two more that could fall under either dba or dbo job are

7. making security changes
8. making schema changes

Internally written databases cause more security and schema changes. With internally developed databases also comes maintaining test and qa instances.

Active directory integration will make security updates less frequent. Its a good idea to have a network login decide weather or not a user has access to the data. The network credentials are securely stored and passed to the database that way to avoid having to store plain text passwords. Its called windows authentication or integrated security. SQL authentication is sometimes easier to configure less secure. In the server properties you can turn off SQL authentication entirely.

After security and schema changes, users generally request changes to increase performance. These actions can be taken to increase performance at the system level.

1. more tempdb data files (1 per core)
2. set SQL max memory so the OS does not have to compete for RAM
3. put data files and log files on separate disks
4. put tempdb files on its own disks
5. set initial file sizes large enough so they do not autogrow
6. -E startup param for larger pages (4MB)
7. 1444 for equal file growth and fill
8. eliminate disk communication bottlenecks (upgrade HBAs and SAN switches)
9. turn on data compression and backup compression (SQL 2008+)

At the database level these actions can produce excellent results

1. decrease lock escalation
2. normalize tables
3. remove unused indexes
4. add needed indexes
5. rebuild or reorganize indexes
6. switch to simple recovery model
7. do not auto-close
8. backup->restore->and grow dbs that might have become fragmented at disk level