Mamy tabelę:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 |
Najnowsze komentarze