Skip to content

Instantly share code, notes, and snippets.

@daiplusplus
Last active March 1, 2022 21:49
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save daiplusplus/4078d2f4526bde007edaf6d217abb10c to your computer and use it in GitHub Desktop.
GO
CREATE TABLE dbo.ReservedWords (
Word sysname NOT NULL
CONSTRAINT PK_ReservedWords PRIMARY KEY ( Word )
);
GO
CREATE FUNCTION dbo.QuoteNameIfNecessary( @name sysname )
RETURNS sysname
AS
BEGIN
DECLARE @qname sysname;
/* https://stackoverflow.com/questions/12246156/repeating-characters-in-t-sql-like-condition */
IF EXISTS( SELECT 1 FROM dbo.ReservedWords WHERE Word = @name )
SET @qname = QUOTENAME( @name, '"' );
ELSE IF @name LIKE '%[^a-zA-Z0-9]%' COLLATE Latin1_General_BIN /* i.e. it contains non-Latin1 alphanumerics. */
SET @qname = QUOTENAME( @name, '"' );
ELSE
SET @qname = @name;
RETURN @qname;
END
GO
INSERT INTO dbo.ReservedWords ( Word )
SELECT [Word] FROM ( VALUES
( '@@CONNECTIONS' ),
( '@@CPU_BUSY' ),
( '@@CURSOR_ROWS' ),
( '@@DATEFIRST' ),
( '@@DBTS' ),
( '@@ERROR' ),
( '@@FETCH_STATUS' ),
( '@@IDENTITY' ),
( '@@IDLE' ),
( '@@IO_BUSY' ),
( '@@LANGID' ),
( '@@LANGUAGE' ),
( '@@LOCK_TIMEOUT' ),
( '@@MAX_CONNECTIONS' ),
( '@@MAX_PRECISION' ),
( '@@NESTLEVEL' ),
( '@@OPTIONS' ),
( '@@PACK_RECEIVED' ),
( '@@PACK_SENT' ),
( '@@PACKET_ERRORS' ),
( '@@PROCID' ),
( '@@REMSERVER' ),
( '@@ROWCOUNT' ),
( '@@SERVERNAME' ),
( '@@SERVICENAME' ),
( '@@SPID' ),
( '@@TEXTSIZE' ),
( '@@TIMETICKS' ),
( '@@TOTAL_ERRORS' ),
( '@@TOTAL_READ' ),
( '@@TOTAL_WRITE' ),
( '@@TRANCOUNT' ),
( '@@VERSION' ),
( '$PARTITION' ),
( 'ABS' ),
( 'ABSOLUTE' ),
( 'ACOS' ),
( 'ACTION' ),
( 'ADA' ),
( 'ADD' ),
( 'ADMIN' ),
( 'AFTER' ),
( 'AGGREGATE' ),
( 'ALIAS' ),
( 'ALL' ),
( 'ALLOCATE' ),
( 'ALTER' ),
( 'AND' ),
( 'ANY' ),
( 'APP_NAME' ),
( 'APPLOCK_MODE' ),
( 'APPLOCK_TEST' ),
( 'APPLY' ),
( 'APPROX_COUNT_DISTINCT' ),
( 'ARE' ),
( 'ARRAY' ),
( 'AS' ),
( 'ASC' ),
( 'ASCII' ),
( 'ASENSITIVE' ),
( 'ASIN' ),
( 'ASSEMBLYPROPERTY' ),
( 'ASSERTION' ),
( 'ASYMKEY_ID' ),
( 'ASYMKEYPROPERTY' ),
( 'ASYMMETRIC' ),
( 'AT' ),
( 'ATAN' ),
( 'ATN2' ),
( 'ATOMIC' ),
( 'AUTHORIZATION' ),
( 'AVG' ),
( 'BACKUP' ),
( 'BEFORE' ),
( 'BEGIN' ),
( 'BETWEEN' ),
( 'bigint' ),
( 'BINARY' ),
( 'BINARY_CHECKSUM' ),
( 'BIT' ),
( 'BIT_LENGTH' ),
( 'BLOB' ),
( 'BOOLEAN' ),
( 'BOTH' ),
( 'BREADTH' ),
( 'BREAK' ),
( 'BROWSE' ),
( 'BULK' ),
( 'BY' ),
( 'CALL' ),
( 'CALLED' ),
( 'CARDINALITY' ),
( 'CASCADE' ),
( 'CASCADED' ),
( 'CASE' ),
( 'CAST' ),
( 'CATALOG' ),
( 'CATCH' ),
( 'CEILING' ),
( 'CERTENCODED' ),
( 'CERTPRIVATEKEY' ),
( 'CHAR' ),
( 'CHAR_LENGTH' ),
( 'CHARACTER' ),
( 'CHARACTER_LENGTH' ),
( 'CHARINDEX' ),
( 'CHECK' ),
( 'CHECKALLOC' ),
( 'CHECKCATALOG' ),
( 'CHECKCONSTRAINTS' ),
( 'CHECKDB' ),
( 'CHECKFILEGROUP' ),
( 'CHECKIDENT' ),
( 'CHECKPOINT' ),
( 'CHECKSUM' ),
( 'CHECKSUM_AGG' ),
( 'CHECKTABLE' ),
( 'CHOOSE' ),
( 'CLASS' ),
( 'CLEANTABLE' ),
( 'CLOB' ),
( 'CLONEDATABASE' ),
( 'CLOSE' ),
( 'CLUSTERED' ),
( 'COALESCE' ),
( 'COL_LENGTH' ),
( 'COL_NAME' ),
( 'COLLATE' ),
( 'COLLATION' ),
( 'COLLATIONPROPERTY' ),
( 'COLLECT' ),
( 'COLUMN' ),
( 'COLUMNPROPERTY' ),
( 'COLUMNS_UPDATED' ),
( 'COMMIT' ),
( 'COMPLETION' ),
( 'COMPRESS' ),
( 'COMPUTE' ),
( 'CONCAT' ),
( 'CONCAT_WS' ),
( 'CONDITION' ),
( 'CONNECT' ),
( 'CONNECTION' ),
( 'CONNECTIONPROPERTY' ),
( 'CONSTRAINT' ),
( 'CONSTRAINTS' ),
( 'CONSTRUCTOR' ),
( 'CONTAINS' ),
( 'CONTAINSTABLE' ),
( 'CONTEXT_INFO' ),
( 'CONTINUE' ),
( 'CONVERT' ),
( 'CORR' ),
( 'CORRESPONDING' ),
( 'COS' ),
( 'COT' ),
( 'COUNT' ),
( 'COUNT_BIG' ),
( 'COVAR_POP' ),
( 'COVAR_SAMP' ),
( 'CREATE' ),
( 'CROSS' ),
( 'CUBE' ),
( 'CUME_DIST' ),
( 'CURRENT' ),
( 'CURRENT_CATALOG' ),
( 'CURRENT_DATE' ),
( 'CURRENT_DEFAULT_TRANSFORM_GROUP' ),
( 'CURRENT_PATH' ),
( 'CURRENT_REQUEST_ID' ),
( 'CURRENT_ROLE' ),
( 'CURRENT_SCHEMA' ),
( 'CURRENT_TIME' ),
( 'CURRENT_TIMESTAMP' ),
( 'CURRENT_TRANSACTION_ID' ),
( 'CURRENT_TRANSFORM_GROUP_FOR_TYPE' ),
( 'CURRENT_USER' ),
( 'CURSOR' ),
( 'CURSOR_STATUS' ),
( 'CYCLE' ),
( 'DATA' ),
( 'DATABASE' ),
( 'DATABASE_PRINCIPAL_ID' ),
( 'DATABASEPROPERTYEX' ),
( 'DATALENGTH' ),
( 'DATE' ),
( 'DATEADD' ),
( 'DATEDIFF' ),
( 'DATEDIFF_BIG' ),
( 'DATEFROMPARTS' ),
( 'DATENAME' ),
( 'DATEPART' ),
( 'datetime' ),
( 'datetime2' ),
( 'DATETIME2FROMPARTS' ),
( 'DATETIMEFROMPARTS' ),
( 'datetimeoffset' ),
( 'DATETIMEOFFSETFROMPARTS' ),
( 'DAY' ),
( 'DB_ID' ),
( 'DB_NAME' ),
( 'DBCC' ),
( 'DBREINDEX' ),
( 'DEALLOCATE' ),
( 'DEC' ),
( 'DECIMAL' ),
( 'DECLARE' ),
( 'DECOMPRESS' ),
( 'DECRYPTBYASYMKEY' ),
( 'DECRYPTBYCERT' ),
( 'DECRYPTBYKEY' ),
( 'DECRYPTBYKEYAUTOASYMKEY' ),
( 'DecryptByKeyAutoCert' ),
( 'DECRYPTBYPASSPHRASE' ),
( 'DEFAULT' ),
( 'DEFERRABLE' ),
( 'DEFERRED' ),
( 'DEGREES' ),
( 'DELETE' ),
( 'DENSE_RANK' ),
( 'DENY' ),
( 'DEPTH' ),
( 'DEREF' ),
( 'DESC' ),
( 'DESCRIBE' ),
( 'DESCRIPTOR' ),
( 'DESTROY' ),
( 'DESTRUCTOR' ),
( 'DETERMINISTIC' ),
( 'DIAGNOSTICS' ),
( 'DICTIONARY' ),
( 'DIFFERENCE' ),
( 'DISCONNECT' ),
( 'DISK' ),
( 'DISTINCT' ),
( 'DISTRIBUTED' ),
( 'DOMAIN' ),
( 'DOUBLE' ),
( 'DROP' ),
( 'DROPCLEANBUFFERS' ),
( 'DUMP' ),
( 'DYNAMIC' ),
( 'EACH' ),
( 'ELEMENT' ),
( 'ELSE' ),
( 'ENCRYPTBYASYMKEY' ),
( 'ENCRYPTBYCERT' ),
( 'ENCRYPTBYKEY' ),
( 'ENCRYPTBYPASSPHRASE' ),
( 'END' ),
( 'END-EXEC' ),
( 'EOMONTH' ),
( 'EQUALS' ),
( 'ERRLVL' ),
( 'ERROR_LINE' ),
( 'ERROR_MESSAGE' ),
( 'ERROR_NUMBER' ),
( 'ERROR_PROCEDURE' ),
( 'ERROR_SEVERITY' ),
( 'ERROR_STATE' ),
( 'ESCAPE' ),
( 'EVENTDATA' ),
( 'EVERY' ),
( 'EXCEPT' ),
( 'EXCEPTION' ),
( 'EXEC' ),
( 'EXECUTE' ),
( 'EXISTS' ),
( 'EXIT' ),
( 'EXP' ),
( 'EXTERNAL' ),
( 'EXTRACT' ),
( 'FALSE' ),
( 'FETCH' ),
( 'FILE' ),
( 'FILE_ID' ),
( 'FILE_IDEX' ),
( 'FILE_NAME' ),
( 'FILEGROUP_ID' ),
( 'FILEGROUP_NAME' ),
( 'FILEGROUPPROPERTY' ),
( 'FILEPROPERTY' ),
( 'FILLFACTOR' ),
( 'FILTER' ),
( 'FIRST' ),
( 'FIRST_VALUE' ),
( 'FLOAT' ),
( 'FLOOR' ),
( 'FLUSHAUTHCACHE' ),
( 'fn_virtualfilestats' ),
( 'FOR' ),
( 'FOREIGN' ),
( 'FORMAT' ),
( 'FORMATMESSAGE' ),
( 'FORTRAN' ),
( 'FOUND' ),
( 'FREE' ),
( 'FREEPROCCACHE' ),
( 'FREESESSIONCACHE' ),
( 'FREESYSTEMCACHE' ),
( 'FREETEXT' ),
( 'FREETEXTTABLE' ),
( 'FROM' ),
( 'FULL' ),
( 'FULLTEXTCATALOGPROPERTY' ),
( 'FULLTEXTSERVICEPROPERTY' ),
( 'FULLTEXTTABLE' ),
( 'FUNCTION' ),
( 'FUSION' ),
( 'GENERAL' ),
( 'geography' ),
( 'geometry' ),
( 'GET' ),
( 'GET_FILESTREAM_TRANSACTION_CONTEXT' ),
( 'GETANSINULL' ),
( 'GETDATE' ),
( 'GETUTCDATE' ),
( 'GLOBAL' ),
( 'GO' ),
( 'GOTO' ),
( 'GRANT' ),
( 'GREATEST' ),
( 'GROUP' ),
( 'GROUPING' ),
( 'GROUPING_ID' ),
( 'GROUPINGS' ),
( 'HASHBYTES' ),
( 'HAVING' ),
( 'HELP' ),
( 'hierarchyid' ),
( 'HOLD' ),
( 'HOLDLOCK' ),
( 'HOST' ),
( 'HOST_ID' ),
( 'HOST_NAME' ),
( 'HOUR' ),
( 'IDENT_CURRENT' ),
( 'IDENT_INCR' ),
( 'IDENT_SEED' ),
( 'IDENTITY' ),
( 'IDENTITY_INSERT' ),
( 'IDENTITYCOL' ),
( 'IF' ),
( 'IGNORE' ),
( 'IIF' ),
( 'image' ),
( 'IMMEDIATE' ),
( 'IN' ),
( 'INCLUDE' ),
( 'INDEX' ),
( 'INDEX_COL' ),
( 'INDEXDEFRAG' ),
( 'INDEXKEY_PROPERTY' ),
( 'INDEXPROPERTY' ),
( 'INDICATOR' ),
( 'INITIALIZE' ),
( 'INITIALLY' ),
( 'INNER' ),
( 'INOUT' ),
( 'INPUT' ),
( 'INPUTBUFFER' ),
( 'INSENSITIVE' ),
( 'INSERT' ),
( 'INT' ),
( 'INTEGER' ),
( 'INTERSECT' ),
( 'INTERSECTION' ),
( 'INTERVAL' ),
( 'INTO' ),
( 'IS' ),
( 'IS_OBJECTSIGNED' ),
( 'ISDATE' ),
( 'ISJSON' ),
( 'ISNULL' ),
( 'ISNUMERIC' ),
( 'ISOLATION' ),
( 'ITERATE' ),
( 'JOIN' ),
( 'JSON_MODIFY' ),
( 'JSON_QUERY' ),
( 'JSON_VALUE' ),
( 'KEY' ),
( 'KEY_GUID' ),
( 'KEY_ID' ),
( 'KEY_NAME' ),
( 'KILL' ),
( 'LABEL' ),
( 'LAG' ),
( 'LANGUAGE' ),
( 'LARGE' ),
( 'LAST' ),
( 'LAST_VALUE' ),
( 'LATERAL' ),
( 'LEAD' ),
( 'LEADING' ),
( 'LEAST' ),
( 'LEFT' ),
( 'LEN' ),
( 'LESS' ),
( 'LEVEL' ),
( 'LIKE' ),
( 'LIKE_REGEX' ),
( 'LIMIT' ),
( 'LINENO' ),
( 'LN' ),
( 'LOAD' ),
( 'LOCAL' ),
( 'LOCALTIME' ),
( 'LOCALTIMESTAMP' ),
( 'LOCATOR' ),
( 'LOG' ),
( 'LOG10' ),
( 'LOWER' ),
( 'LTRIM' ),
( 'MAP' ),
( 'MATCH' ),
( 'MAX' ),
( 'MEMBER' ),
( 'MERGE' ),
( 'METHOD' ),
( 'MIN' ),
( 'MIN_ACTIVE_ROWVERSION' ),
( 'MINUTE' ),
( 'MOD' ),
( 'MODIFIES' ),
( 'MODIFY' ),
( 'MODULE' ),
( 'money' ),
( 'MONTH' ),
( 'MULTISET' ),
( 'NAMES' ),
( 'NATIONAL' ),
( 'NATURAL' ),
( 'NCHAR' ),
( 'NCLOB' ),
( 'NEW' ),
( 'NEWID' ),
( 'NEWSEQUENTIALID' ),
( 'NEXT' ),
( 'NO' ),
( 'NOCHECK' ),
( 'NONCLUSTERED' ),
( 'NONE' ),
( 'NORMALIZE' ),
( 'NOT' ),
( 'ntext' ),
( 'NTILE' ),
( 'NULL' ),
( 'NULLIF' ),
( 'NUMERIC' ),
( 'nvarchar' ),
( 'OBJECT' ),
( 'OBJECT_DEFINITION' ),
( 'OBJECT_ID' ),
( 'OBJECT_NAME' ),
( 'OBJECT_SCHEMA_NAME' ),
( 'OBJECTPROPERTY' ),
( 'OBJECTPROPERTYEX' ),
( 'OCCURRENCES_REGEX' ),
( 'OCTET_LENGTH' ),
( 'OF' ),
( 'OFF' ),
( 'OFFSETS' ),
( 'OLD' ),
( 'ON' ),
( 'ONLY' ),
( 'OPEN' ),
( 'OPENDATASOURCE' ),
( 'OPENJSON' ),
( 'OPENQUERY' ),
( 'OPENROWSET' ),
( 'OPENTRAN' ),
( 'OPENXML' ),
( 'OPERATION' ),
( 'OPTION' ),
( 'OR' ),
( 'ORDER' ),
( 'ORDINALITY' ),
( 'ORIGINAL_DB_NAME' ),
( 'OUT' ),
( 'OUTER' ),
( 'OUTPUT' ),
( 'OUTPUTBUFFER' ),
( 'OVER' ),
( 'OVERLAPS' ),
( 'OVERLAY' ),
( 'PAD' ),
( 'PARAMETER' ),
( 'PARAMETERS' ),
( 'PARSE' ),
( 'PARSENAME' ),
( 'PARTIAL' ),
( 'PARTITION' ),
( 'PASCAL' ),
( 'PATH' ),
( 'PATINDEX' ),
( 'PERCENT' ),
( 'PERCENT_RANK' ),
( 'PERCENTILE_CONT' ),
( 'PERCENTILE_DISC' ),
( 'PI' ),
( 'PIVOT' ),
( 'PLAN' ),
( 'POSITION' ),
( 'POSITION_REGEX' ),
( 'POSTFIX' ),
( 'POWER' ),
( 'PRECISION' ),
( 'PREDICT' ),
( 'PREFIX' ),
( 'PREORDER' ),
( 'PREPARE' ),
( 'PRESERVE' ),
( 'PRIMARY' ),
( 'PRINT' ),
( 'PRIOR' ),
( 'PRIVILEGES' ),
( 'PROC' ),
( 'PROCCACHE' ),
( 'PROCEDURE' ),
( 'PUBLIC' ),
( 'PUBLISHINGSERVERNAME' ),
( 'QUOTENAME' ),
( 'RADIANS' ),
( 'RAISERROR' ),
( 'RAND' ),
( 'RANGE' ),
( 'RANK' ),
( 'READ' ),
( 'READS' ),
( 'READTEXT' ),
( 'REAL' ),
( 'RECONFIGURE' ),
( 'RECURSIVE' ),
( 'REF' ),
( 'REFERENCES' ),
( 'REFERENCING' ),
( 'REGR_AVGX' ),
( 'REGR_AVGY' ),
( 'REGR_COUNT' ),
( 'REGR_INTERCEPT' ),
( 'REGR_R2' ),
( 'REGR_SLOPE' ),
( 'REGR_SXX' ),
( 'REGR_SXY' ),
( 'REGR_SYY' ),
( 'RELATIVE' ),
( 'RELEASE' ),
( 'REPLACE' ),
( 'REPLICATE' ),
( 'REPLICATION' ),
( 'RESTORE' ),
( 'RESTRICT' ),
( 'RESULT' ),
( 'RETURN' ),
( 'RETURNS' ),
( 'REVERSE' ),
( 'REVERT' ),
( 'REVOKE' ),
( 'RIGHT' ),
( 'ROLE' ),
( 'ROLLBACK' ),
( 'ROLLUP' ),
( 'ROUND' ),
( 'ROUTINE' ),
( 'ROW' ),
( 'ROW_NUMBER' ),
( 'ROWCOUNT' ),
( 'ROWCOUNT_BIG' ),
( 'ROWGUIDCOL' ),
( 'ROWS' ),
( 'rowversion' ),
( 'RTRIM' ),
( 'RULE' ),
( 'SAVE' ),
( 'SAVEPOINT' ),
( 'SCHEMA' ),
( 'SCHEMA_ID' ),
( 'SCHEMA_NAME' ),
( 'SCOPE' ),
( 'SCOPE_IDENTITY' ),
( 'SCROLL' ),
( 'SEARCH' ),
( 'SECOND' ),
( 'SECTION' ),
( 'SECURITYAUDIT' ),
( 'SELECT' ),
( 'SEMANTICKEYPHRASETABLE' ),
( 'SEMANTICSIMILARITYDETAILSTABLE' ),
( 'SEMANTICSIMILARITYTABLE' ),
( 'SENSITIVE' ),
( 'SEQUENCE' ),
( 'SERVERPROPERTY' ),
( 'SESSION' ),
( 'SESSION_CONTEXT' ),
( 'SESSION_ID' ),
( 'SESSION_USER' ),
( 'SET' ),
( 'SETS' ),
( 'SETUSER' ),
( 'SHOW_STATISTICS' ),
( 'SHOWCONTIG' ),
( 'SHRINKDATABASE' ),
( 'SHRINKFILE' ),
( 'SHUTDOWN' ),
( 'SIGN' ),
( 'SIGNBYASYMKEY' ),
( 'SIGNBYCERT' ),
( 'SIMILAR' ),
( 'SIN' ),
( 'SIZE' ),
( 'smalldatetime' ),
( 'SMALLDATETIMEFROMPARTS' ),
( 'SMALLINT' ),
( 'smallmoney' ),
( 'SOME' ),
( 'SOUNDEX' ),
( 'SPACE' ),
( 'SPECIFIC' ),
( 'SPECIFICTYPE' ),
( 'SQL' ),
( 'sql_variant' ),
( 'SQL_VARIANT_PROPERTY' ),
( 'SQLCA' ),
( 'SQLCODE' ),
( 'SQLERROR' ),
( 'SQLEXCEPTION' ),
( 'SQLPERF' ),
( 'SQLSTATE' ),
( 'SQLWARNING' ),
( 'SQRT' ),
( 'SQUARE' ),
( 'START' ),
( 'STATE' ),
( 'STATEMENT' ),
( 'STATIC' ),
( 'STATISTICS' ),
( 'STATS_DATE' ),
( 'STDDEV_POP' ),
( 'STDDEV_SAMP' ),
( 'STDEV' ),
( 'STDEVP' ),
( 'STR' ),
( 'STRING_AGG' ),
( 'STRING_ESCAPE' ),
( 'STRING_SPLIT' ),
( 'STRUCTURE' ),
( 'STUFF' ),
( 'SUBMULTISET' ),
( 'SUBSTRING' ),
( 'SUBSTRING_REGEX' ),
( 'SUM' ),
( 'SWITCHOFFSET' ),
( 'SYMKEYPROPERTY' ),
( 'SYMMETRIC' ),
( 'SYSDATETIME' ),
( 'SYSDATETIMEOFFSET' ),
( 'SYSTEM' ),
( 'SYSTEM_USER' ),
( 'SYSUTCDATETIME' ),
( 'TABLE' ),
( 'TABLESAMPLE' ),
( 'TAN' ),
( 'TEMPORARY' ),
( 'TERMINATE' ),
( 'TERTIARY_WEIGHTS' ),
( 'text' ),
( 'TEXTPTR' ),
( 'TEXTSIZE' ),
( 'TEXTVALID' ),
( 'THAN' ),
( 'THEN' ),
( 'THROW' ),
( 'TIME' ),
( 'TIMEFROMPARTS' ),
( 'TIMESTAMP' ),
( 'TIMEZONE_HOUR' ),
( 'TIMEZONE_MINUTE' ),
( 'tinyint' ),
( 'TO' ),
( 'TODATETIMEOFFSET' ),
( 'TOP' ),
( 'TRACEOFF' ),
( 'TRACEON' ),
( 'TRACESTATUS' ),
( 'TRAILING' ),
( 'TRAN' ),
( 'TRANSACTION' ),
( 'TRANSLATE' ),
( 'TRANSLATE_REGEX' ),
( 'TRANSLATION' ),
( 'TREAT' ),
( 'TRIGGER' ),
( 'TRIGGER_NESTLEVEL' ),
( 'TRIM' ),
( 'TRUE' ),
( 'TRUNCATE' ),
( 'TRY' ),
( 'TRY_CAST' ),
( 'TRY_CONVERT' ),
( 'TRY_PARSE' ),
( 'TSEQUAL' ),
( 'TYPE_ID' ),
( 'TYPE_NAME' ),
( 'TYPEPROPERTY' ),
( 'UESCAPE' ),
( 'UNDER' ),
( 'UNICODE' ),
( 'UNION' ),
( 'UNIQUE' ),
( 'uniqueidentifier' ),
( 'UNKNOWN' ),
( 'UNNEST' ),
( 'UNPIVOT' ),
( 'UPDATE' ),
( 'UPDATETEXT' ),
( 'UPDATEUSAGE' ),
( 'UPPER' ),
( 'USAGE' ),
( 'USE' ),
( 'USER' ),
( 'USEROPTIONS' ),
( 'USING' ),
( 'VALUE' ),
( 'VALUES' ),
( 'VAR' ),
( 'VAR_POP' ),
( 'VAR_SAMP' ),
( 'varbinary' ),
( 'VARCHAR' ),
( 'VARIABLE' ),
( 'VARP' ),
( 'VARYING' ),
( 'VERIFYSIGNEDBYASMKEY' ),
( 'VERIGYSIGNEDBYCERT' ),
( 'VERSION' ),
( 'VIEW' ),
( 'WAITFOR' ),
( 'WHEN' ),
( 'WHENEVER' ),
( 'WHERE' ),
( 'WHILE' ),
( 'WIDTH_BUCKET' ),
( 'WINDOW' ),
( 'WITH' ),
( 'WITHIN' ),
( 'WITHIN GROUP' ),
( 'WITHOUT' ),
( 'WORK' ),
( 'WRITE' ),
( 'WRITETEXT' ),
( 'XACT_STATE' ),
( 'xml' ),
( 'XMLAGG' ),
( 'XMLATTRIBUTES' ),
( 'XMLBINARY' ),
( 'XMLCAST' ),
( 'XMLCOMMENT' ),
( 'XMLCONCAT' ),
( 'XMLDOCUMENT' ),
( 'XMLELEMENT' ),
( 'XMLEXISTS' ),
( 'XMLFOREST' ),
( 'XMLITERATE' ),
( 'XMLNAMESPACES' ),
( 'XMLPARSE' ),
( 'XMLPI' ),
( 'XMLQUERY' ),
( 'XMLSERIALIZE' ),
( 'XMLTABLE' ),
( 'XMLTEXT' ),
( 'XMLVALIDATE' ),
( 'YEAR' ),
( 'ZONE' )
) AS words( [Word] );
GO
/* Some quick test-cases: *
SELECT 'hello' AS Input, dbo.QuoteNameIfNecessary( 'hello' ) AS Result
UNION ALL
SELECT 'bigint' AS Input, dbo.QuoteNameIfNecessary( 'bigint' ) AS Result
UNION ALL
SELECT 'big int' AS Input, dbo.QuoteNameIfNecessary( 'big int' ) AS Result
UNION ALL
SELECT 'bi"g"int' AS Input, dbo.QuoteNameIfNecessary( 'bi"g"int' ) AS Result;
*/
-- As seen on StackOverflow:
-- https://stackoverflow.com/questions/39088616/create-sql-server-user-defined-table-type-based-on-a-table/71246577#71246577
-- Replace `QUOTENAME` with `dbo.QuoteNameIfNecessary` if you like.
CREATE FUNCTION dbo.ShowCreateTypeFromTables(
@identityColMode tinyint = 1, /* 0 = Exclude IDENTITY, 1 = Include, without IDENTITY property, 2 = Include, with IDENTITY property */
@includeComputed bit = 0,
@includeGenerated bit = 0,
@includeHistoryTables bit = 0
)
RETURNS TABLE
AS
RETURN
WITH
pkCols AS ( /* Columns participating in PRIMARY KEY constraints, `PK_Ordinal` is for composite PKs. */
SELECT
kcu.TABLE_SCHEMA,
kcu.TABLE_NAME,
QUOTENAME( kcu.COLUMN_NAME, '"' ) AS COLUMN_NAME,
kcu.ORDINAL_POSITION AS PK_Ordinal
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON
tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
AND
tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
WHERE
tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
),
pkDefExprs AS ( /* PRIMARY KEY definition expressions */
SELECT
k.TABLE_SCHEMA,
k.TABLE_NAME,
CONCAT(
CHAR(13),CHAR(10), /* CRLF \r\n */
CHAR(13),CHAR(10), /* CRLF \r\n */
CHAR(9), /* TAB \t */
'PRIMARY KEY ( ',
STRING_AGG( k.COLUMN_NAME, /*separator:*/ ', ' ) WITHIN GROUP ( ORDER BY k.PK_Ordinal ),
' )'
) AS PKDefinition
FROM
pkCols AS k
GROUP BY
k.TABLE_SCHEMA,
k.TABLE_NAME
),
ckDefExprs AS ( /* CHECK constraint definition expressions */
SELECT
ccs.TABLE_SCHEMA,
ccs.TABLE_NAME,
STRING_AGG( ccs.CKExpr, /*separator:*/ CONCAT(',', CHAR(13), CHAR(10) ) ) WITHIN GROUP ( ORDER BY ccs.CONSTRAINT_NAME ) AS AllCKExprs
FROM
(
SELECT
SCHEMA_NAME( t.schema_id ) AS TABLE_SCHEMA,
OBJECT_NAME( t.object_id ) AS TABLE_NAME,
cc."name" AS CONSTRAINT_NAME,
cc."definition",
CONCAT( CHAR(9) /* TAB \t */, 'CONSTRAINT CHECK ', cc."definition" ) AS CKExpr /* cc.[definition] includes outer parens. */
FROM
sys.check_constraints AS cc
INNER JOIN sys.tables AS t ON cc.parent_object_id = t.object_id
WHERE
cc.is_disabled = 0
) AS ccs
GROUP BY
ccs.TABLE_SCHEMA,
ccs.TABLE_NAME
),
ukDefExprs AS ( /* UNIQUE constraint definition expressions */
SELECT
ucs.TABLE_SCHEMA,
ucs.TABLE_NAME,
STRING_AGG( UKExpr, /*separator:*/ CONCAT(',', CHAR(13), CHAR(10) ) ) WITHIN GROUP ( ORDER BY ucs.CONSTRAINT_NAME ) AS AllUKExprs
FROM
(
SELECT
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
tc.CONSTRAINT_NAME,
STRING_AGG( cc.COLUMN_NAME, /*separator:*/ ', ' ) WITHIN GROUP ( ORDER BY c.ORDINAL_POSITION ) AS UKExpr
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS cc ON
tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
AND
tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS c ON
cc.TABLE_SCHEMA = c.TABLE_SCHEMA
AND
cc.TABLE_NAME = c.TABLE_NAME
AND
cc.COLUMN_NAME = c.COLUMN_NAME
WHERE
tc.CONSTRAINT_TYPE = 'UNIQUE'
GROUP BY
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
tc.CONSTRAINT_NAME
) AS ucs
GROUP BY
ucs.TABLE_SCHEMA,
ucs.TABLE_NAME
),
maxColNameLengths AS (
SELECT
c.TABLE_SCHEMA,
c.TABLE_NAME,
MAX( LEN( QUOTENAME( c.COLUMN_NAME, '"' ) ) ) AS MaxColNameLen
FROM
INFORMATION_SCHEMA.COLUMNS AS c
GROUP BY
c.TABLE_SCHEMA,
c.TABLE_NAME
),
zombocols AS ( /* The only limit is YOURSELF */
SELECT
c1.TABLE_SCHEMA,
c1.TABLE_NAME,
c1.COLUMN_NAME,
QUOTENAME( c1.COLUMN_NAME, '"' ) AS COLUMN_NAME_Q,
LEFT( CONCAT( QUOTENAME( c1.COLUMN_NAME, '"' ), SPACE( ml.MaxColNameLen ) ), ml.MaxColNameLen ) AS COLUMN_NAME_Q_PAD,
(
CASE
WHEN c1.DATETIME_PRECISION IS NOT NULL THEN CONCAT( c1.DATA_TYPE, '(', c1.DATETIME_PRECISION, ')' ) /* e.g. `datetime2(7)`, `datetimeoffset(7)`, etc. */
WHEN c1.DATA_TYPE IN ( 'timestamp', 'rowversion' ) THEN 'rowversion' /* c1 uses `timestamp` as the type name, which is archaic. */
WHEN c1.DATA_TYPE IN ( 'decimal', 'numeric' ) THEN CONCAT( c1.DATA_TYPE, '(', c1.NUMERIC_PRECISION, ',', c1.NUMERIC_SCALE, ')' ) /* e.g. `datetime2(7)`, `datetimeoffset(7)`, etc. */
WHEN c1.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND c1.CHARACTER_MAXIMUM_LENGTH = -1 THEN CONCAT( c1.DATA_TYPE, '(max)' ) /* e.g. `nvarchar(max), etc. */
WHEN c1.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CONCAT( c1.DATA_TYPE, '(', c1.CHARACTER_MAXIMUM_LENGTH, ')' ) /* e.g. `nvarchar(255), etc. */
ELSE c1.DATA_TYPE
END
) AS ColTypeExpr,
( CASE c1.IS_NULLABLE WHEN 'YES' THEN ' NULL' ELSE 'NOT NULL' END ) AS ColNullDecl,
c1.ORDINAL_POSITION,
( ROW_NUMBER() OVER ( PARTITION BY c1.TABLE_SCHEMA, c1.TABLE_NAME ORDER BY c1.ORDINAL_POSITION ) - 1 ) AS TableTypeColIndex,
c2.is_identity,
c2.generated_always_type_desc
FROM
INFORMATION_SCHEMA.COLUMNS AS c1
INNER JOIN INFORMATION_SCHEMA.TABLES AS t1 ON
c1.TABLE_SCHEMA = t1.TABLE_SCHEMA
AND
c1.TABLE_NAME = t1.TABLE_NAME
INNER JOIN sys.tables AS t2 ON
t2."name" = t1.TABLE_NAME
AND
SCHEMA_NAME( t2.schema_id ) = t1.TABLE_SCHEMA
INNER JOIN sys.columns AS c2 ON
c2."name" = c1.COLUMN_NAME
AND
c2."object_id" = t2."object_id"
INNER JOIN maxColNameLengths AS ml ON
ml.TABLE_SCHEMA = c1.TABLE_SCHEMA
AND
ml.TABLE_NAME = c1.TABLE_NAME
WHERE
(
( @includeHistoryTables = 0 AND t2.temporal_type_desc IN ( 'NON_TEMPORAL_TABLE', 'SYSTEM_VERSIONED_TEMPORAL_TABLE' ) /* i.e. exclude 'HISTORY_TABLE' */ )
OR
( @includeHistoryTables = 1 )
)
AND
(
( @includeGenerated = 0 AND c2.generated_always_type = 0 )
OR
( @includeGenerated = 1 )
)
AND
(
( @includeComputed = 0 AND c2.is_computed = 0 )
OR
( @includeComputed = 1 )
)
AND
(
( @identityColMode = 0 AND c2.is_identity = 0 )
OR
( @identityColMode IN ( 1, 2 ) )
)
),
createTableColumnExprs AS (
SELECT
CONCAT(
CHAR(9), /* \t TAB */
c.COLUMN_NAME_Q_PAD, ' ',
LEFT( CONCAT( c.ColTypeExpr, SPACE(17) ), 17 ), ' ',
c.ColNullDecl, ' ',
CASE WHEN c.is_identity = 1 AND @identityColMode = 2 THEN 'IDENTITY' ELSE '' END,
'/* ', c.TableTypeColIndex, ' / ', c.ORDINAL_POSITION, ' */'
) AS ColumnExpr,
c.*
FROM
zombocols AS c
),
createTableExprs AS (
SELECT
e.TABLE_SCHEMA,
e.TABLE_NAME,
STRING_AGG( e.ColumnExpr, /*separator:*/ CONCAT(',', CHAR(13), CHAR(10) ) ) WITHIN GROUP ( ORDER BY e.ORDINAL_POSITION ) AS ColumnExprs
FROM
createTableColumnExprs AS e
GROUP BY
e.TABLE_SCHEMA,
e.TABLE_NAME
)
SELECT
ct.TABLE_SCHEMA,
ct.TABLE_NAME,
CONCAT(
'CREATE TYPE ', ct.TABLE_SCHEMA, '.TypeOf_', ct.TABLE_NAME, ' AS TABLE (',
CONCAT(CHAR(13),CHAR(10)),
ct.ColumnExprs,
ISNULL( pke.PKDefinition, '' ),
ISNULL( cke.AllCKExprs , '' ),
CONCAT(CHAR(13),CHAR(10)),
');',
CONCAT(CHAR(13),CHAR(10))
) AS CreateTypeExpr
FROM
createTableExprs AS ct
LEFT OUTER JOIN pkDefExprs AS pke ON
ct.TABLE_SCHEMA = pke.TABLE_SCHEMA
AND
ct.TABLE_NAME = pke.TABLE_NAME
LEFT OUTER JOIN ckDefExprs AS cke ON
ct.TABLE_SCHEMA = cke.TABLE_SCHEMA
AND
ct.TABLE_NAME = cke.TABLE_NAME;
GO
CREATE FUNCTION dbo.ShowCreateTypeFromTable( @schema sysname, @tableName sysname ) RETURNS nvarchar(max)
AS
BEGIN
DECLARE @stmt nvarchar(max) = NULL;
SELECT
@stmt = e.CreateTypeExpr
FROM
dbo.ShowCreateTypeFromTables() AS e
WHERE
e.TABLE_SCHEMA = @schema
AND
e.TABLE_NAME = @tableName;
RETURN @stmt;
END
@daiplusplus
Copy link
Author

daiplusplus commented Feb 24, 2022

Possible improvements:

  • Using CONCAT( '[', name, ']' ) is incorrect. Use QUOTENAME instead.
    • Better yet: define a FUNCTION util.EscapeNameOnlyIfNecessary( @name sysname ) RETURNS sysname
      • It would use a persisted single-column TABLE util.Keywords containing all T-SQL reserved keywords, type-names, and so on.
      • It would return @name verbatim unless @name is in utilKeywords, or @name contains non-0-9A-Za-z characters.
  • Temporal table support:
    • _History tables should not be included.
    • Temporal GENERATED columns should be excluded.
  • Exclude computed columns - or include them? CREATE TYPE supports them.
    • But there is value in including IDENTITY columns, though.
  • Generate INDEX statements.
    • Don't simply match TABLE indexes; instead generate INDEX statements for each foreign-key constraint in the source TABLE, as those are likely to be used in procs. (Of course it's up to the end-user to decide which indexes they want to use in the types by commenting-out the generated syntax).
  • Generate UNIQUE constraints (not the same thing as a UNIQUE INDEX, ofc).
  • Generate C# SqlDataReader and SqlDataRecord call-sites.
  • Generate MERGE statements that can be used in procedures with table-valued parameters using the generated table-type.

See all supported features in https://docs.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment