blob: 620fe9dbc512cdabd67604c2ca76ef146b97bd29 (
plain)
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Tango.Core.Components
{
public static class DataBaseDescriptionsHelper
{
public class DbDescription
{
public String TableName { get; set; }
public String ColumnName { get; set; }
public String TableDescription { get; set; }
public String ColumnDescription { get; set; }
}
public class ForeignKeyDescription
{
public String TableName { get; set; }
public String ColumnName { get; set; }
}
public static List<DbDescription> GetDescriptions(DbConnection connection)
{
List<DbDescription> dbDescriptions = new List<DbDescription>();
var command = connection.CreateCommand();
command.CommandText = @"SELECT u.name + '.' + t.name AS [table],
td.value AS [table_desc],
c.name AS [column],
cd.value AS [column_desc]
FROM sysobjects t
INNER JOIN sysusers u
ON u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
ON td.major_id = t.id
AND td.minor_id = 0
AND td.name = 'MS_Description'
INNER JOIN syscolumns c
ON c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
ON cd.major_id = c.id
AND cd.minor_id = c.colid
AND cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY t.name, c.colorder";
DataTable table = new DataTable();
table.Load(command.ExecuteReader());
foreach (var row in table.Rows.OfType<DataRow>())
{
String table_name = row.ItemArray.GetValue(0).ToString().Replace("dbo.", "");
String table_decription = row.ItemArray.GetValue(1).ToString();
String column_name = row.ItemArray.GetValue(2).ToString();
String column_description = row.ItemArray.GetValue(3).ToString();
if (!String.IsNullOrWhiteSpace(table_decription) || !String.IsNullOrWhiteSpace(column_description))
{
dbDescriptions.Add(new DbDescription()
{
TableName = table_name,
TableDescription = table_decription,
ColumnName = column_name,
ColumnDescription = column_description,
});
}
}
return dbDescriptions;
}
public static List<ForeignKeyDescription> GetForeignKeys(DbConnection connection)
{
List<ForeignKeyDescription> keys = new List<ForeignKeyDescription>();
var command = connection.CreateCommand();
command.CommandText = "SELECT a.TABLE_NAME, a.COLUMN_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE a ON a.CONSTRAINT_CATALOG = b.CONSTRAINT_CATALOG AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME";
DataTable table = new DataTable();
table.Load(command.ExecuteReader());
foreach (var row in table.Rows.OfType<DataRow>())
{
String table_name = row.ItemArray.GetValue(0).ToString();
String column_name = row.ItemArray.GetValue(1).ToString();
keys.Add(new ForeignKeyDescription()
{
TableName = table_name,
ColumnName = column_name
});
}
return keys;
}
}
}
|
