Apr
17
0

Исследование структуры базы данных через таблицы sys.objects, sys.columns, sys.types

Author osya    Category Системный анализ     Tags ,

Системные таблицы sys.sysobjects, sys.syscolumns, sys.systypes являются устаревшими и оставлены для обратной совместимости. Вместо этих таблиц следует использовать таблицы sys.objects, sys.columns и sys.types соответственно.

Далее рассмотрим несколько запросов с использованием этих таблиц:

--Объекты БД и соответствующие колонки
select o.name, c.name
from sys.objects o
  inner join sys.columns c on c.object_id = o.object_id
order by 1, 2
--Объекты БД и соответствующие поля с типами и максимальной длиной
select o.name, c.name, t.name, c.max_length
  from sys.objects o
  inner join sys.columns c on c.object_id = o.object_id
  inner join sys.types t on c.system_type_id = t.system_type_id
where t.user_type_id = t.system_type_id
order by 1, 2, 3
--Объекты БД и количество полей в них
select o.name, count(c.name)
  from sys.objects o
  inner join sys.columns c on c.object_id = o.object_id
group by o.name
order by 1
--Все пользовательские таблицы БД, содержащие поля типа image или ntext
select o.name, c.name, t.name, c.max_length
  from sys.objects o
  inner join sys.columns c on c.object_id = o.object_id
  inner join sys.types t on c.system_type_id = t.system_type_id
where t.user_type_id = t.system_type_id
  and t.name in ('image', 'ntext')
  and o.type = 'U'
order by 1, 2, 3
--Все пользовательские таблицы БД, содержащие поля типа image или ntext, и количество полей в них
select t.name, COUNT(c.name)
from
  (select o.object_id, o.name
  from sys.objects o
    inner join sys.columns c on c.object_id = o.object_id
    inner join sys.types t on c.system_type_id = t.system_type_id
  where t.user_type_id = t.system_type_id
    and t.name in ('image', 'ntext')
    and o.type = 'U'
  ) t
  left join sys.columns c on c.object_id = t.object_id
group by t.name
order by 2
--Все пользовательские таблицы, содержащие
--1. Поле типа image или ntext
--2. Поле типа nvarchar длины 2000
select distinct o.name, count(c.name)
from sys.objects o
  left join sys.columns c on o.object_id = c.object_id
  inner join
  (select o.object_id
  from sys.objects o
    inner join sys.columns c on c.object_id = o.object_id
    inner join sys.types t on c.system_type_id = t.system_type_id
  where t.user_type_id = t.system_type_id
    and t.name in ('image', 'ntext')
    and o.type = 'U'
  ) t1 on o.object_id = t1.object_id
  inner join
  (select o.object_id
  from sys.objects o
    inner join sys.columns c on c.object_id = o.object_id
    inner join sys.types t on c.system_type_id = t.system_type_id
  where t.user_type_id = t.system_type_id
    and t.name = 'nvarchar'
    and c.max_length = 2000
    and o.type = 'U'
  ) t2 on o.object_id = t2.object_id
group by o.name

Оказывается, в MS SQL комментарии к полям таблиц и к самим таблицам добавляются неочевидным путём – через хранимую процедуру sys.sp_addextendedproperty.

Post comment