Проверка на существование таблиц и других объектов баз данных MS SQL

ALTER FUNCTION [dbo].[is_tbl_exists]
(
@tbl_name VARCHAR(50)
)
RETURNS BIT
AS
BEGIN
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'' + @tbl_name AND xtype='U')
 RETURN 1

RETURN 0
END


-- xtype='V' - views
--  xtype='FN' - functions
--  xtype='P' - procedures

Сравнение текста процедур, представлений, функций и т.д. в двух базах

--Коллеги из филиала спросили "как понять, какие процедуры изменились?"

--К счастью, на том же сервере присутствует база, которую можно считать "эталоном".

--Вместо Base1 и Base2 подставить требуемые базы данных, в том числе можно --сравнивать определения объектов на linked серверах.

--В этом случае не нужно применять сторонние утилиты - достаточно одного запроса: T---SQL:

SELECT o.name
FROM sys.objects o
LEFT join sys.columns col ON col.OBJECT_ID = o.OBJECT_ID
WHERE o.type = 'U'
and col.name like 'Date'
ORDER BY o.nameSELECT
   isnull(s1.name+'.'+o1.[name], s2.name+'.'+o2.[name]) ,
   CASE
      WHEN o1.[OBJECT_ID] IS null THEN 'только во второй'
      WHEN o2.[OBJECT_ID] IS null THEN 'только в первой'
   ELSE 'объект изменен' END
FROM Base1.sys.sql_modules m1
INNER join Base1.sys.objects o1
ON o1.[OBJECT_ID] = m1.[OBJECT_ID]
LEFT join Base1.sys.schemas s1
   ON s1.[schema_id] = o1.[schema_id]
FULL join Base2.sys.sql_modules m2
   INNER join Base2.sys.objects o2
      ON o2.[OBJECT_ID] = m2.[OBJECT_ID]
   LEFT join Base2.sys.schemas s2
      ON s2.[schema_id] = o2.[schema_id]
ON s1.name+'.'+o1.[name] = s2.name+'.'+o2.[name]
WHERE isnull(m1.definition, '') <> isnull(m2.definition, '+')

Выбрать все таблицы у которых нет поля ID?

SELECT o.name
FROM sys.objects o
LEFT join sys.columns col ON col.OBJECT_ID = o.OBJECT_ID
WHERE o.type = 'U'
and col.name like 'Date'
ORDER BY o.name

Выбрать все таблицы у которых нет поля ID?

SELECT o.name
FROM sys.objects o
LEFT join sys.columns col ON col.OBJECT_ID = o.OBJECT_ID
WHERE o.type = 'U'
and col.name like 'Date'
ORDER BY o.name

Сколько таблицы используют на диске места?

DROP TABLE used

CREATE TABLE used (
   _name VARCHAR(150),
   _rows INT,
   reserved VARCHAR(100),
   DATA VARCHAR(100),
   index_size VARCHAR(100),
   unused VARCHAR(100)
)

EXEC sp_msforeachtable 'INSERT INTO used exec sp_spaceused ''?'''

UPDATE used
SET
reserved = REPLACE(reserved, ' KB', ''),
DATA = REPLACE(DATA, ' KB', ''),
index_size = REPLACE(index_size, ' KB', ''),
unused = REPLACE(unused, ' KB', '')


ALTER TABLE used ALTER COLUMN reserved INT

ALTER TABLE used ALTER COLUMN DATA INT
ALTER TABLE used ALTER COLUMN index_size INT
ALTER TABLE used ALTER COLUMN unused INT


SELECT * FROM used t
ORDER BY reserved DESC

Удаление внешних ключей в SQL Server

SELECT 'ALTER TABLE ' + o.name + '
    DROP CONSTRAINT ' + fk.name FROM sys.foreign_keys fk
join sys.objects o ON o.OBJECT_ID = fk.parent_object_id

Чистка БД в SQL Server

1. Удаление временных таблиц
SELECT 'drop table [' + s2.name + '].[' + s.name + ']' FROM sysobjects s JOIN sysusers s2 ON s.uid = s2.uid WHERE (s.name LIKE 'stratum_inter%' OR s.name LIKE 'map_production%') OR s2.name not in('public','dbo','guest','INFORMATION_SCHEMA','sys','sqladmin','db_owner','db_accessadmin','db_securityadmin','db_ddladmin','db_backupoperator','db_datareader','db_datawriter','db_denydatareader','db_denydatawriter')  


2. Удаление Пользовательских схем
SELECT 'DROP SCHEMA ['+name +']'  FROM sys.schemas AS s WHERE s.name like 'CORP%' or
s.name like 'RNTC%' or
s.name like 'BUILTIN%' or
s.name not in('public','dbo','guest','INFORMATION_SCHEMA','sys','sqladmin','db_owner','db_accessadmin','db_securityadmin','db_ddladmin','db_backupoperator','db_datareader','db_datawriter','db_denydatareader','db_denydatawriter'


3. Удаление пользователей базы
--так более праввильно SELECT DISTINCT 'DROP USER [' + o.name + ']' FROM dbo.sysusers o WHERE o.name like 'CORP%' or
o.name like 'RNTC%' or
o.name like 'BUILTIN%' or
o.name not in('public','dbo','guest','INFORMATION_SCHEMA','sys','sqladmin','db_owner','db_accessadmin','db_securityadmin','db_ddladmin','db_backupoperator','db_datareader','db_datawriter','db_denydatareader','db_denydatawriter') --если не сработает, то можно попробовать так SELECT DISTINCT 'EXEC sp_dropuser ''' + o.name + '''' FROM dbo.sysusers o WHERE o.name like 'CORP%' or
o.name like 'RNTC%' or
o.name like 'BUILTIN%'


Как посмотреть к какой схеме привязан пользователь
SELECT s.name AS [schema_name], dp1.name AS [owner_name] FROM sys.schemas AS s INNER JOIN sys.database_principals AS dp1 ON dp1.principal_id = s.principal_id