USE [eda7k_localDB]


--Получить все колонки таблицы
SELECT *
FROM eda7k_localDB.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = N'users'
--Получить все колонки таблицы
SELECT *
FROM sys.columns
WHERE object_id = OBJECT_ID('SeregaTheDed_SQLLogin_1.users');


--Получить все автовычисляемые колонки
select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME


--Получить как вычисляется вычисляемая колонка
SELECT definition
FROM sys.computed_columns
WHERE object_id = object_id(N'SeregaTheDed_SQLLogin_1.users')



--Проставить всем дефолтные значения
SELECT
    'ALTER TABLE '+ SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id) +
    ' ADD CONSTRAINT ' + dc.name + ' DEFAULT(' + definition
    + ') FOR ' + c.name
FROM sys.default_constraints dc
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id



--получить все внешние ключи
SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id


--Пример скрипата
--alter table SeregaTheDed_SQLLogin_1.products
    --add constraint products_products_price_fk
        --foreign key (price) references SeregaTheDed_SQLLogin_1.products (price)
--go


--EXEC sp_fkeys @pktable_name = 'rel_orders_products', @pktable_owner = 'SeregaTheDed_SQLLogin_1'--Не робит