ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) AS ,ĬAST(ISNULL(NUMERIC_PRECISION, 0) AS INT) AS ,ĬAST(ISNULL(DATETIME_PRECISION, 0) AS INT) AS DateTimePrecision,ĬAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS IsIdentity,ĬAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') = 1 THEN 1 This is the code I use within the EntityFramework Reverse POCO Generator (available here)ĬAST(CASE WHEN IS_NULLABLE = 'YES' THEN 1 If you want to get a description an object of the current database (and schema) simple type: EXEC sp_desc 'YourTable' Īs sp_desc is a system marked procedure, you can even leave the exec command, too (not recommended anyway): sp_desc 'YourTable' To execute the procedure type: EXEC sp_desc '' PRINT sp_executesql sys.sp_MS_marksystemobject sp_desc ON a.NAME COLLATE Hungarian_CI_AS = b.NAME COLLATE Hungarian_CI_AS WHEN ''varchar'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')'' WHEN ''varbinary'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')'' WHEN ''nvarchar'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')'' WHEN ''numeric'' THEN DATA_TYPE + ''('' + CAST(NUMERIC_PRECISION AS VARCHAR) + '', '' + CAST(NUMERIC_SCALE AS VARCHAR) + '')'' WHEN ''char'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')'' PRINT 'Object is missing from your function call!' The above mentioned select can be used in a system marked stored procedure and it can be called from any database of your instance on a simple way: USE master ON a.NAME COLLATE YourCollation = b.NAME COLLATE YourCollation ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', 'column', default) WHEN 'varchar' THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' WHEN 'varbinary' THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' WHEN 'nvarchar' THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' WHEN 'numeric' THEN DATA_TYPE + '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')' WHEN 'char' THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' I wrote an sql*plus DESC(RIBE) like select (displays the column comments, too) in t-sql: USE YourDB
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |