SQL Server'da Tabloya Ait Kolon Bilgilerini Öğrenmek

Herkese merhaba,

Bu kısımda SQL Server’da tabloya ait kolon bilgilerini öğrenme kodları olacak.

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

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

Author Details

Yavuz Selim Kart

http://inovatifkimyadergisi.com/

y.selimkart@gmail.com

– tarafında kendini ilerletmeye çalışırken bildiklerini sürekli başkalarına aktaran, yazılım dünyasında (C#, Java, Python vb.) kendini güncel tutmaya çalışan, araştırma yapmaktan bıkmayan, meraklı, bilginin üzerine bilgi katmaktan mutluluk duyan bir yazılım eğitmeni ve kod sever. – Grafik tasarım dünyasında Indesign’ı anlatacak kadar iyi kullanabilen, Photoshop ve Illustrator’e de orta düzey hakimiyeti bulunan, internet üzerinde online kimya dergisi kurmuş ve bunu yüzbinlerce kişiye ulaştırmayı başaran bir grafik sever. -WordPress tarafında 5 adet web sitesi kurmuş, bu sistemler üzerine çalışan bir WordPress sever. -Sosyal medya üzerinde okumalara ve araştırmalara devam eden, kendi kurmuş olduğu kimya dergisini daha çok kişiye nasıl ulaştırırım diye düşünen ve bu konuda SEO çalışmaları üzerine kafa yoran bir Sosyal Medya sever. Kısaca çok çalışmayı seven, azimli biriyim.