Manchmal möchte man Infos über die Spaltern einer bestimmte Tabelle
oder einer ganze Datenbank abrufen. Ich habe 2 Queries geschrieben mit den man diverse Informationen bekommt.
Schemainformationen für 1 Tabelle:
|
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 |
DECLARE @tablename VARCHAR(500) = 'myTable'; SELECT c.name AS ColumnName, t.name AS DataType, c.max_length AS max_length, c.is_nullable, c.is_identity, CASE WHEN (SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = @tablename AND COLUMN_NAME = c.name) IS NULL THEN 0 ELSE 1 END AS is_PrimaryKey, right(left(def.definition, len(def.definition)-1), len(left(def.definition, len(def.definition)-1))-1) AS DefaultValue FROM sys.columns c INNER JOIN sys.types t ON t.system_type_id = c.system_type_id LEFT JOIN (select definition,@tablename AS Tablename,c.name AS ColumnName from sys.default_constraints dc join sys.columns c on c.object_id = dc.parent_object_id and c.column_id = dc.parent_column_id LEFT OUTER JOIN ( SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = @tablename) PrimaryKey ON PrimaryKey.COLUMN_NAME = c.name where parent_object_id = object_id(@tablename) and c.name = c.name) def ON def.ColumnName = c.name AND def.Tablename = @tablename WHERE object_id = OBJECT_ID(@tablename) ORDER BY c.column_id ASC |
Schemainformationen für ganze Datenbank:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT tab.name AS TableName, col.name As ColumnName, typ.name AS DataType, col.max_length AS max_length, col.is_nullable, col.is_identity, CASE WHEN (SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = tab.name AND COLUMN_NAME = col.name) IS NULL THEN 0 ELSE 1 END AS is_PrimaryKey, right(left(def.definition, len(def.definition)-1), len(left(def.definition, len(def.definition)-1))-1) AS DefaultValue FROM sys.tables AS tab INNER JOIN sys.columns col ON col.object_id = OBJECT_ID(tab.name) INNER JOIN sys.types typ ON typ.system_type_id = col.system_type_id LEFT JOIN (select definition,c.name AS ColumnName from sys.default_constraints dc join sys.columns c on c.object_id = dc.parent_object_id and c.column_id = dc.parent_column_id ) def ON col.name = def.ColumnName Order by 1,2 |
Login