Find objects and schemas in a Microsoft SQL Server database whose names are reserved words

find-objects-schemas-with-reserved-word-names.sql

-- use table variable to store list of reserved words
DECLARE @reserved TABLE (
	Word VARCHAR(255) NOT NULL
);

INSERT INTO @reserved ( Word )	
VALUES 
('ADD'),
('EXISTS'),
('PRECISION'),
('ALL'),
('EXIT'),
('PRIMARY'),
('ALTER'),
('EXTERNAL'),
('PRINT'),
('AND'),
('FETCH'),
('PROC'),
('ANY'),
('FILE'),
('PROCEDURE'),
('AS'),
('FILLFACTOR'),
('PUBLIC'),
('ASC'),
('FOR'),
('RAISERROR'),
('AUTHORIZATION'),
('FOREIGN'),
('READ'),
('BACKUP'),
('FREETEXT'),
('READTEXT'),
('BEGIN'),
('FREETEXTTABLE'),
('RECONFIGURE'),
('BETWEEN'),
('FROM'),
('REFERENCES'),
('BREAK'),
('FULL'),
('REPLICATION'),
('BROWSE'),
('FUNCTION'),
('RESTORE'),
('BULK'),
('GOTO'),
('RESTRICT'),
('BY'),
('GRANT'),
('RETURN'),
('CASCADE'),
('GROUP'),
('REVERT'),
('CASE'),
('HAVING'),
('REVOKE'),
('CHECK'),
('HOLDLOCK'),
('RIGHT'),
('CHECKPOINT'),
('IDENTITY'),
('ROLLBACK'),
('CLOSE'),
('IDENTITY_INSERT'),
('ROWCOUNT'),
('CLUSTERED'),
('IDENTITYCOL'),
('ROWGUIDCOL'),
('COALESCE'),
('IF'),
('RULE'),
('COLLATE'),
('IN'),
('SAVE'),
('COLUMN'),
('INDEX'),
('SCHEMA'),
('COMMIT'),
('INNER'),
('SECURITYAUDIT'),
('COMPUTE'),
('INSERT'),
('SELECT'),
('CONSTRAINT'),
('INTERSECT'),
('SESSION_USER'),
('CONTAINS'),
('INTO'),
('SET'),
('CONTAINSTABLE'),
('IS'),
('SETUSER'),
('CONTINUE'),
('JOIN'),
('SHUTDOWN'),
('CONVERT'),
('KEY'),
('SOME'),
('CREATE'),
('KILL'),
('STATISTICS'),
('CROSS'),
('LEFT'),
('SYSTEM_USER'),
('CURRENT'),
('LIKE'),
('TABLE'),
('CURRENT_DATE'),
('LINENO'),
('TABLESAMPLE'),
('CURRENT_TIME'),
('LOAD'),
('TEXTSIZE'),
('CURRENT_TIMESTAMP'),
('MERGE'),
('THEN'),
('CURRENT_USER'),
('NATIONAL'),
('TO'),
('CURSOR'),
('NOCHECK'),
('TOP'),
('DATABASE'),
('NONCLUSTERED'),
('TRAN'),
('DBCC'),
('NOT'),
('TRANSACTION'),
('DEALLOCATE'),
('NULL'),
('TRIGGER'),
('DECLARE'),
('NULLIF'),
('TRUNCATE'),
('DEFAULT'),
('OF'),
('TSEQUAL'),
('DELETE'),
('OFF'),
('UNION'),
('DENY'),
('OFFSETS'),
('UNIQUE'),
('DESC'),
('ON'),
('UNPIVOT'),
('DISK'),
('OPEN'),
('UPDATE'),
('DISTINCT'),
('OPENDATASOURCE'),
('UPDATETEXT'),
('DISTRIBUTED'),
('OPENQUERY'),
('USE'),
('DOUBLE'),
('OPENROWSET'),
('USER'),
('DROP'),
('OPENXML'),
('VALUES'),
('DUMP'),
('OPTION'),
('VARYING'),
('ELSE'),
('OR'),
('VIEW'),
('END'),
('ORDER'),
('WAITFOR'),
('ERRLVL'),
('OUTER'),
('WHEN'),
('ESCAPE'),
('OVER'),
('WHERE'),
('EXCEPT'),
('PERCENT'),
('WHILE'),
('EXEC'),
('PIVOT'),
('WITH'),
('EXECUTE'),
('PLAN'),
('WRITETEXT'),
(''),
(''),
('ABSOLUTE'),
('EXEC'),
('OVERLAPS'),
('ACTION'),
('EXECUTE'),
('PAD'),
('ADA'),
('EXISTS'),
('PARTIAL'),
('ADD'),
('EXTERNAL'),
('PASCAL'),
('ALL'),
('EXTRACT'),
('POSITION'),
('ALLOCATE'),
('FALSE'),
('PRECISION'),
('ALTER'),
('FETCH'),
('PREPARE'),
('AND'),
('FIRST'),
('PRESERVE'),
('ANY'),
('FLOAT'),
('PRIMARY'),
('ARE'),
('FOR'),
('PRIOR'),
('AS'),
('FOREIGN'),
('PRIVILEGES'),
('ASC'),
('FORTRAN'),
('PROCEDURE'),
('ASSERTION'),
('FOUND'),
('PUBLIC'),
('AT'),
('FROM'),
('READ'),
('AUTHORIZATION'),
('FULL'),
('REAL'),
('AVG'),
('GET'),
('REFERENCES'),
('BEGIN'),
('GLOBAL'),
('RELATIVE'),
('BETWEEN'),
('GO'),
('RESTRICT'),
('BIT'),
('GOTO'),
('REVOKE'),
('BIT_LENGTH'),
('GRANT'),
('RIGHT'),
('BOTH'),
('GROUP'),
('ROLLBACK'),
('BY'),
('HAVING'),
('ROWS'),
('CASCADE'),
('HOUR'),
('SCHEMA'),
('CASCADED'),
('IDENTITY'),
('SCROLL'),
('CASE'),
('IMMEDIATE'),
('SECOND'),
('CAST'),
('IN'),
('SECTION'),
('CATALOG'),
('INCLUDE'),
('SELECT'),
('CHAR'),
('INDEX'),
('SESSION'),
('CHAR_LENGTH'),
('INDICATOR'),
('SESSION_USER'),
('CHARACTER'),
('INITIALLY'),
('SET'),
('CHARACTER_LENGTH'),
('INNER'),
('SIZE'),
('CHECK'),
('INPUT'),
('SMALLINT'),
('CLOSE'),
('INSENSITIVE'),
('SOME'),
('COALESCE'),
('INSERT'),
('SPACE'),
('COLLATE'),
('INT'),
('SQL'),
('COLLATION'),
('INTEGER'),
('SQLCA'),
('COLUMN'),
('INTERSECT'),
('SQLCODE'),
('COMMIT'),
('INTERVAL'),
('SQLERROR'),
('CONNECT'),
('INTO'),
('SQLSTATE'),
('CONNECTION'),
('IS'),
('SQLWARNING'),
('CONSTRAINT'),
('ISOLATION'),
('SUBSTRING'),
('CONSTRAINTS'),
('JOIN'),
('SUM'),
('CONTINUE'),
('KEY'),
('SYSTEM_USER'),
('CONVERT'),
('LANGUAGE'),
('TABLE'),
('CORRESPONDING'),
('LAST'),
('TEMPORARY'),
('COUNT'),
('LEADING'),
('THEN'),
('CREATE'),
('LEFT'),
('TIME'),
('CROSS'),
('LEVEL'),
('TIMESTAMP'),
('CURRENT'),
('LIKE'),
('TIMEZONE_HOUR'),
('CURRENT_DATE'),
('LOCAL'),
('TIMEZONE_MINUTE'),
('CURRENT_TIME'),
('LOWER'),
('TO'),
('CURRENT_TIMESTAMP'),
('MATCH'),
('TRAILING'),
('CURRENT_USER'),
('MAX'),
('TRANSACTION'),
('CURSOR'),
('MIN'),
('TRANSLATE'),
('DATE'),
('MINUTE'),
('TRANSLATION'),
('DAY'),
('MODULE'),
('TRIM'),
('DEALLOCATE'),
('MONTH'),
('TRUE'),
('DEC'),
('NAMES'),
('UNION'),
('DECIMAL'),
('NATIONAL'),
('UNIQUE'),
('DECLARE'),
('NATURAL'),
('UNKNOWN'),
('DEFAULT'),
('NCHAR'),
('UPDATE'),
('DEFERRABLE'),
('NEXT'),
('UPPER'),
('DEFERRED'),
('NO'),
('USAGE'),
('DELETE'),
('NONE'),
('USER'),
('DESC'),
('NOT'),
('USING'),
('DESCRIBE'),
('NULL'),
('VALUE'),
('DESCRIPTOR'),
('NULLIF'),
('VALUES'),
('DIAGNOSTICS'),
('NUMERIC'),
('VARCHAR'),
('DISCONNECT'),
('OCTET_LENGTH'),
('VARYING'),
('DISTINCT'),
('OF'),
('VIEW'),
('DOMAIN'),
('ON'),
('WHEN'),
('DOUBLE'),
('ONLY'),
('WHENEVER'),
('DROP'),
('OPEN'),
('WHERE'),
('ELSE'),
('OPTION'),
('WITH'),
('END'),
('OR'),
('WORK'),
('END-EXEC'),
('ORDER'),
('WRITE'),
('ESCAPE'),
('OUTER'),
('YEAR'),
('EXCEPT'),
('OUTPUT'),
('ZONE'),
('EXCEPTION'),
(''),
('ABSOLUTE'),
('HOST'),
('RELATIVE'),
('ACTION'),
('HOUR'),
('RELEASE'),
('ADMIN'),
('IGNORE'),
('RESULT'),
('AFTER'),
('IMMEDIATE'),
('RETURNS'),
('AGGREGATE'),
('INDICATOR'),
('ROLE'),
('ALIAS'),
('INITIALIZE'),
('ROLLUP'),
('ALLOCATE'),
('INITIALLY'),
('ROUTINE'),
('ARE'),
('INOUT'),
('ROW'),
('ARRAY'),
('INPUT'),
('ROWS'),
('ASENSITIVE'),
('INT'),
('SAVEPOINT'),
('ASSERTION'),
('INTEGER'),
('SCROLL'),
('ASYMMETRIC'),
('INTERSECTION'),
('SCOPE'),
('AT'),
('INTERVAL'),
('SEARCH'),
('ATOMIC'),
('ISOLATION'),
('SECOND'),
('BEFORE'),
('ITERATE'),
('SECTION'),
('BINARY'),
('LANGUAGE'),
('SENSITIVE'),
('BIT'),
('LARGE'),
('SEQUENCE'),
('BLOB'),
('LAST'),
('SESSION'),
('BOOLEAN'),
('LATERAL'),
('SETS'),
('BOTH'),
('LEADING'),
('SIMILAR'),
('BREADTH'),
('LESS'),
('SIZE'),
('CALL'),
('LEVEL'),
('SMALLINT'),
('CALLED'),
('LIKE_REGEX'),
('SPACE'),
('CARDINALITY'),
('LIMIT'),
('SPECIFIC'),
('CASCADED'),
('LN'),
('SPECIFICTYPE'),
('CAST'),
('LOCAL'),
('SQL'),
('CATALOG'),
('LOCALTIME'),
('SQLEXCEPTION'),
('CHAR'),
('LOCALTIMESTAMP'),
('SQLSTATE'),
('CHARACTER'),
('LOCATOR'),
('SQLWARNING'),
('CLASS'),
('MAP'),
('START'),
('CLOB'),
('MATCH'),
('STATE'),
('COLLATION'),
('MEMBER'),
('STATEMENT'),
('COLLECT'),
('METHOD'),
('STATIC'),
('COMPLETION'),
('MINUTE'),
('STDDEV_POP'),
('CONDITION'),
('MOD'),
('STDDEV_SAMP'),
('CONNECT'),
('MODIFIES'),
('STRUCTURE'),
('CONNECTION'),
('MODIFY'),
('SUBMULTISET'),
('CONSTRAINTS'),
('MODULE'),
('SUBSTRING_REGEX'),
('CONSTRUCTOR'),
('MONTH'),
('SYMMETRIC'),
('CORR'),
('MULTISET'),
('SYSTEM'),
('CORRESPONDING'),
('NAMES'),
('TEMPORARY'),
('COVAR_POP'),
('NATURAL'),
('TERMINATE'),
('COVAR_SAMP'),
('NCHAR'),
('THAN'),
('CUBE'),
('NCLOB'),
('TIME'),
('CUME_DIST'),
('NEW'),
('TIMESTAMP'),
('CURRENT_CATALOG'),
('NEXT'),
('TIMEZONE_HOUR'),
('CURRENT_DEFAULT_TRANSFORM_GROUP'),
('NO'),
('TIMEZONE_MINUTE'),
('CURRENT_PATH'),
('NONE'),
('TRAILING'),
('CURRENT_ROLE'),
('NORMALIZE'),
('TRANSLATE_REGEX'),
('CURRENT_SCHEMA'),
('NUMERIC'),
('TRANSLATION'),
('CURRENT_TRANSFORM_GROUP_FOR_TYPE'),
('OBJECT'),
('TREAT'),
('CYCLE'),
('OCCURRENCES_REGEX'),
('TRUE'),
('DATA'),
('OLD'),
('UESCAPE'),
('DATE'),
('ONLY'),
('UNDER'),
('DAY'),
('OPERATION'),
('UNKNOWN'),
('DEC'),
('ORDINALITY'),
('UNNEST'),
('DECIMAL'),
('OUT'),
('USAGE'),
('DEFERRABLE'),
('OVERLAY'),
('USING'),
('DEFERRED'),
('OUTPUT'),
('VALUE'),
('DEPTH'),
('PAD'),
('VAR_POP'),
('DEREF'),
('PARAMETER'),
('VAR_SAMP'),
('DESCRIBE'),
('PARAMETERS'),
('VARCHAR'),
('DESCRIPTOR'),
('PARTIAL'),
('VARIABLE'),
('DESTROY'),
('PARTITION'),
('WHENEVER'),
('DESTRUCTOR'),
('PATH'),
('WIDTH_BUCKET'),
('DETERMINISTIC'),
('POSTFIX'),
('WITHOUT'),
('DICTIONARY'),
('PREFIX'),
('WINDOW'),
('DIAGNOSTICS'),
('PREORDER'),
('WITHIN'),
('DISCONNECT'),
('PREPARE'),
('WORK'),
('DOMAIN'),
('PERCENT_RANK'),
('WRITE'),
('DYNAMIC'),
('PERCENTILE_CONT'),
('XMLAGG'),
('EACH'),
('PERCENTILE_DISC'),
('XMLATTRIBUTES'),
('ELEMENT'),
('POSITION_REGEX'),
('XMLBINARY'),
('END-EXEC'),
('PRESERVE'),
('XMLCAST'),
('EQUALS'),
('PRIOR'),
('XMLCOMMENT'),
('EVERY'),
('PRIVILEGES'),
('XMLCONCAT'),
('EXCEPTION'),
('RANGE'),
('XMLDOCUMENT'),
('FALSE'),
('READS'),
('XMLELEMENT'),
('FILTER'),
('REAL'),
('XMLEXISTS'),
('FIRST'),
('RECURSIVE'),
('XMLFOREST'),
('FLOAT'),
('REF'),
('XMLITERATE'),
('FOUND'),
('REFERENCING'),
('XMLNAMESPACES'),
('FREE'),
('REGR_AVGX'),
('XMLPARSE'),
('FULLTEXTTABLE'),
('REGR_AVGY'),
('XMLPI'),
('FUSION'),
('REGR_COUNT'),
('XMLQUERY'),
('GENERAL'),
('REGR_INTERCEPT'),
('XMLSERIALIZE'),
('GET'),
('REGR_R2'),
('XMLTABLE'),
('GLOBAL'),
('REGR_SLOPE'),
('XMLTEXT'),
('GO'),
('REGR_SXX'),
('XMLVALIDATE'),
('GROUPING'),
('REGR_SXY'),
('YEAR'),
('HOLD'),
('REGR_SYY');

-- search objects
SELECT 
	S.name AS SchemaName
	, OBJ.name AS ObjectName
	, OBJ.type_desc AS ObjectType
	
FROM sys.objects AS OBJ
	INNER JOIN sys.schemas AS S ON ( OBJ.schema_id = S.schema_id )
	
WHERE OBJ.name IN ( 
		SELECT Word
		FROM @reserved
	)
	
ORDER BY S.name, OBJ.name;


-- search for schema names

SELECT name AS SchemaName

FROM sys.schemas

WHERE name IN ( 
		SELECT Word
		FROM @reserved
	)
	
ORDER BY name;