SQL Server’da Tabloya Ait Kolon Bilgilerini Öğrenmek

Herkese merhaba,

Bu yazıda SQL Server’da tabloya ait kolon bilgilerinin nasıl öğrenileceği ile ilgili bilgi paylaşacağım.

SQL Server’da bazı durumlarda tabloya ait kolon bilgilerini öğrenmek isteyebilirsiniz.

Aşağıdaki kodu kullanarak sizler de bu işlemi rahatlıkla yapabilirsiniz.

DECLARE @TableName SYSNAME = 'Categories'
SELECT
	DB_NAME() AS [Database Name]
   ,s.name AS [Schema Name]
   ,t.name AS [Table Name]
   ,c.name AS [Column Name]
   ,ty.name AS [Column Data Type]
   ,uty.name AS [Column System Type]
   ,c.max_length AS [Column Maximum Length]
   ,c.precision AS [Column Precision]
   ,c.scale AS [Column Scale]
   ,CASE c.is_nullable
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS [Column Is Nullable]
   ,CASE c.is_identity
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS [Column Has Identity]
   ,CASE c.is_computed
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS [Column Is Computed]
   ,cc.definition AS [Computed Column Definition]
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
	ON t.schema_id = s.schema_id
INNER JOIN sys.columns AS c
	ON t.object_id = c.object_id
INNER JOIN sys.types AS ty
	ON ty.user_type_id = c.user_type_id
INNER JOIN sys.types AS uty
	ON ty.system_type_id = uty.user_type_id
LEFT JOIN sys.computed_columns AS cc
	ON t.object_id = cc.object_id
		AND cc.column_id = c.column_id
WHERE t.name = @TableName
OR @TableName IS NULL
UNION ALL
SELECT
	DB_NAME() AS DatabaseName
   ,s.name AS SchemaName
   ,t.name AS TableName
   ,c.name AS ColumnName
   ,ty.name AS ColumnDataType
   ,ty.name AS ColumnSystemTypeName
   ,c.max_length AS ColumnMaximumLength
   ,c.precision AS ColumnPrecision
   ,c.scale AS ColumnScale
   ,CASE c.is_nullable
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS ColumnIsNullable
   ,CASE c.is_identity
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS ColumnHasIdentity
   ,CASE c.is_computed
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS ColumnIsComputed
   ,cc.definition AS [Computed Column Definition]
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
	ON t.schema_id = s.schema_id
INNER JOIN sys.columns AS c
	ON t.object_id = c.object_id
INNER JOIN sys.types AS ty
	ON ty.user_type_id = c.user_type_id
		AND ty.is_assembly_type = 1
LEFT JOIN sys.computed_columns AS cc
	ON t.object_id = cc.object_id
		AND cc.column_id = c.column_id
WHERE t.name = @TableName
OR @TableName IS NULL

Herkese çalışma hayatında ve yaşamında başarılar kolaylıklar.