[T-SQL] gdzie jest schemat typów tabel

Mamy tabelę:

typy1

W jaki sposób wyświetlić podobny rezultat przy pomocy SQL? Dane z opisem tabel przechowywane są w trzech tabelach: SYS.TABLES, SYS.COLUMNS, SYS.TYPES. Funkcja która wykorzystuje te tabele:

ALTER FUNCTION [dbo].[Getliteraldatatype](@TableName  AS VARCHAR(100),
                                          @ColumnName AS VARCHAR(100))
returns VARCHAR(100)
AS
  BEGIN
      DECLARE @DataType AS VARCHAR(100)
      DECLARE @FullDataType AS VARCHAR(100)
      DECLARE @MaxLength AS INT
      DECLARE @Precision AS INT
      DECLARE @Scale AS INT

      SELECT TOP 1 @DataType = y.NAME,
                   @MaxLength = c.max_length,
                   @Precision = c.PRECISION,
                   @Scale = c.scale
      FROM   sys.tables t
             INNER JOIN sys.columns c
                     ON t.object_id = c.object_id
             INNER JOIN sys.types y
                     ON y.system_type_id = c.system_type_id
      WHERE  t.NAME = @TableName
             AND c.NAME = @ColumnName

      IF @DataType IN ( 'decimal', 'numeric' )
        BEGIN
            SET @FullDataType = Upper(@DataType) + '('
                                + Cast(@Precision AS VARCHAR) + ','
                                + Cast(@Scale AS VARCHAR) + ')'
        END
      ELSE IF @DataType IN ( 'varchar', 'char', 'binary', 'varbinary' )
        BEGIN
            SET @FullDataType = Upper(@DataType) + '('
                                + Cast(@MaxLength AS VARCHAR) + ')'
        END
      ELSE IF @DataType IN ( 'nvarchar', 'nchar' )
        BEGIN
            SET @FullDataType = Upper(@DataType) + '('
                                + Cast(@MaxLength/2 AS VARCHAR) + ')'
        END
      ELSE IF @DataType IN ( 'datetime2', 'datetimeoffset', 'time' )
        BEGIN
            SET @FullDataType = Upper(@DataType) + '('
                                + Cast(@Scale AS VARCHAR) + ')'
        END
      ELSE
        BEGIN
            SET @FullDataType = Upper(@DataType)
        END

      RETURN @FullDataType
  END 

Przykład użycia:

SELECT c.NAME                                 AS 'Column Name',
       dbo.Getliteraldatatype(t.NAME, c.NAME) AS 'Data Type',
       c.is_nullable                          AS 'Allow Nulls'
FROM   sys.tables t
       INNER JOIN sys.columns c
               ON t.object_id = c.object_id
       INNER JOIN sys.types ty
               ON c.user_type_id = ty.user_type_id
WHERE  t.NAME = 'Types'
ORDER  BY t.NAME 

typy2
Share Button

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Time limit is exhausted. Please reload the CAPTCHA.