SQL Server finding sql reserved words used as column names

tsm1rwdh 于 2023-04-04 发布在其他

关注(0)|答案(2)|浏览(149)

i'm just getting started (transititioning to) SQL Server, unfortunately the company has developers that have been doing all the table scripting, etc. on a cursory glance i found several tables that have column names that are also SQL reserved words. in DB2 for IBM i, i could just query the system catalogs for reserved words & am wondering if anyone has a script(or stored procedure) that will do that for SQL Server (currently on version 2012) my goal is to clean this up and have the tables rebuilt with proper column names and a script would be orders of magnitude faster than looking at each table's column listing.

sql-server

来源：https://stackoverflow.com/questions/40535968/finding-sql-reserved-words-used-as-column-names

2条答案

按热度按时间

wqsoz72f1#

Stripped and modified from a validation routine

For SQL Server's Reserved Words

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

Select A.* 
 From  INFORMATION_SCHEMA.COLUMNS  A
 Join  @Reserved 
   on  Column_Name = Word

赞(0）回复(0）举报 2023-04-04

rryofs0p2#

Here is the list of reserved words https://msdn.microsoft.com/en-us/library/ms189822.aspx

You could copy these into a text editor or something then clean it up a little. Then throw them into a table then use that table as a reference.

赞(0）回复(0）举报 2023-04-04