repos_main/数据库/SQL示例脚本/常用SQL语句.sql

/***********断开某个数据库所有连接***********/
USE master
GO
-- 立即设置数据为单用户模式(IMMEDIATE会断开所有连接);
ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
/*
--查看是否还有用户连接
SELECT * FROM sys.sysprocesses WHERE DB_NAME([dbid])='DBName'
或者:
SELECT * FROM master..sysprocesses WHERE DB_NAME([dbid])='DBName'
*/
GO
--再设置回多用户模式即可;
ALTER DATABASE [DBName] SET MULTI_USER 
GO

/***********判断表,字段,视图是否存在***********/
-- 1.判断表是否存在;
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'表名') AND type in (N'U'))
	PRINT '表不存在'
ELSE
	PRINT '表存在'

-- 示例：
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[client]') AND type in (N'U'))
	BEGIN
		PRINT '表不存在'
	END
ELSE
	BEGIN
		PRINT '表已存在'
	END


/**************************************************************************************************/
-- 2.判断字段是否存在;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('表名') AND name='字段名')
	PRINT '表字段不存在'
ELSE
	PRINT '表字段已存在'

IF NOT EXISTS(select * FROM syscolumns WHERE id=object_id('表名') AND name='字段名')
	PRINT '表字段不存在'
ELSE
	PRINT '表字段已存在'

-- 示例：
IF NOT EXISTS (SELECT name FROM syscolumns WHERE id=object_id('client') AND name='i1d')
	BEGIN
		PRINT '表字段不存在'
		ALTER TABLE [dbo].[client] ADD [FolderNames] [nvarchar](max) null
	END
ELSE
	BEGIN
		PRINT '表字段已存在'
		ALTER TABLE [dbo].[client] ALTER COLUMN [FolderNames] [nvarchar](max) null
	END

-- 示例：
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=object_id('client') AND name='id')
	BEGIN
		PRINT '表字段不存在'
	END
ELSE
	BEGIN
		PRINT '表字段已存在'
	END

/**************************************************************************************************/
-- 3.判断视图是否存在;
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'视图名'))
	PRINT '视图不存在'
ELSE
	PRINT '视图已存在'

-- 示例：
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'digitalview'))
	PRINT '视图不存在'
ELSE
	PRINT '视图已存在'

--示例：
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[digitalview]'))
BEGIN
		DROP VIEW [dbo].[digitalview]
		EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[digitalview] as select     
		id, 
		name1, 
		name2, 
		taoxiname, 
		taoxijiage, 
		waiter5, 
		time8, 
		waiter7, 
		time9, 
		waiter4, 
		time10, 
		status4, 
		status6, 
		status5, 
		txtype,
		(SELECT SUM(CAST(money AS float)) AS Expr1 FROM dbo.dindanbukuan WHERE (bz = ''选片二销'') AND (id = dbo.dindan.id)) AS money2,
		(SELECT SUM(CONVERT(float, ps)) AS Expr2 FROM dbo.dindansp WHERE (id = dbo.dindan.id)) AS ps,
		(SELECT SUM(CONVERT(float, zs)) AS Expr3 FROM dbo.dindansp AS dindansp_1 WHERE (id = dbo.dindan.id)) AS zs 
		FROM  dbo.dindan'
END


/**************************************************************************************************/
-- 4.判断字段长度是否符合长度;
IF NOT EXISTS(select * FROM syscolumns WHERE id=object_id('表名') AND name='字段名' and length <> 48)
	PRINT '表字段长度不符合要求'
ELSE
	PRINT '表字段长度已符合要求'

-- 示例：
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id=object_id('client') AND name='id' and length <= 48)
	PRINT '表字段长度不符合要求'
ELSE
	PRINT '表字段长度已符合要求'


/**************************************************************************************************/
-- 5.判断字段长度是否符合长度;
IF NOT EXISTS (select object_name(id)表名, c.name 字段名, t.name 数据类型, c.prec 长度 from syscolumns c inner join systypes t on c.xusertype=t.xusertype where objectproperty(id,'IsUserTable')=1 and id=object_id('表名') and c.name = '字段名' and t.name = '数据类型')
	PRINT '字段类型不符合要求'
ELSE
	PRINT '字段类型已符合要求'

-- 示例：
IF NOT EXISTS (select c.name from syscolumns c inner join systypes t on c.xusertype=t.xusertype where id=object_id('client') and c.name = 'id' and t.name = 'nvarchar')
	PRINT '字段类型不符合要求'
ELSE
	PRINT '字段类型已符合要求'
	
/*
开启CLR功能:
在SQL查询窗口中,分步执行以下命令行;
*/

use [db]
--1.设置数据库的Owner为sa用户;
exec sp_changedbowner 'sa'
go

--2.设置数据库为可信任模式;
alter database [db] set trustworthy on;

--3.开启clr; 
exec sp_configure 'show advanced options', '1';
go
reconfigure;
go

exec sp_configure 'clr enabled', '1'
go
reconfigure;
--如果执行失败,就执行'reconfigure with override'

go
exec sp_configure 'show advanced options', '1'
go