sql 工具函数代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 sql 工具函数
作者:步帝 / 发布于2015/3/24/ 829

	--select ObjectProperty(Object_ID( 'ReportSummaryStatisticLog'),'IsUserTable')
	
	-- 运行环境 sql server

	--判断数据库是否存在
	if exists(select * from master..sysdatabases where name=N'HealthService')
	 print 'exists HealthService'
	else
	 print 'not exists HealthService '

	---------------
	-- 判断要创建的表名是否存在
	if exists (SELECT ObjectProperty(Object_ID( 'ReportSummaryStatisticLog'),'IsUserTable'))
	BEGIN
	-- 删除表
	--drop table [dbo].[ReportSummaryStatisticLog]
	print 'exist ReportSummaryStatisticLog'
	END
	ELSE
	BEGIN
	print 'NOT exist ReportSummaryStatisticLog'
	END
	GO

	-- this sql is wrong
	--if exists (select * from dbo.sysobjects where id= object_id(N'[dbo].[ReportSummaryStatisticLog]')
	-- and OBJECTPROPERTY(id,N'IsUserTable') = 1)
	--BEGIN
	-- -- 删除表
	-- --drop table [dbo].[ReportSummaryStatisticLog]
	-- print 'exist ReportSummaryStatisticLog'
	--END
	--ELSE
	--BEGIN
	-- print 'NOT exist ReportSummaryStatisticLog'
	--END
	--GO

	---------------
	--判断要创建临时表是否存在
	If Object_Id( 'Tempdb.dbo.#Test ') Is Not Null
	 Begin
	 print 'exist #Test'
	 End
	Else
	 Begin
	 print 'not exist #Test'
	 create table #Test (FIELD1 INT,FIELD2 INT)
	 End
	
	-- this sql is wrong
	---- 判断要创建的存储过程名是否存在
	--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_AddBloodPressure]')
	-- and OBJECTPROPERTY(id,N'IsProcedure') = 1)
	--BEGIN
	-- -- 删除存储过程
	-- --drop procedure [dbo].[sp_AddBloodPressure]
	-- print 'exist sp_AddBloodPressure'
	--END
	--ELSE
	--BEGIN
	-- print 'NOT exist sp_AddBloodPressure'
	--END
	--GO
	
	-- this sql is wrong
	---- 判断要创建的视图名是否存在
	--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_UserSurveyLog]')
	-- and OBJECTPROPERTY(id,N'IsView') = 1)
	--BEGIN

	-- -- 删除视图
	-- --drop view [dbo].[view_UserSurveyLog]
	-- print 'exist view_UserSurveyLog'
	--END
	--ELSE
	--BEGIN
	-- print 'NOT exist view_UserSurveyLog'
	--END
	--GO
	
	-- this sql is wrong
	---- 判断要创建的函数名是否存在
	--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_GetAge]')
	-- and xtype in (N'FN', N'IF', N'TF'))
	--BEGIN
	-- -- 删除函数
	-- --drop function [dbo].[fn_GetAge]
	-- print 'exist fn_GetAge'
	--END
	--ELSE
	--BEGIN
	-- print 'NOT exist fn_GetAge'
	--END
	--GO

	-- 判断表是否存在
	-- this sql is wrong
	--if exists (select 1 from sys.objects where name = 'ReportSummaryStatisticLog')
	-- begin print 'exist ReportSummaryStatisticLog'end
	--else
	-- begin print ' not exist ReportSummaryStatisticLog'end

	if exists (select ObjectProperty(Object_ID( 'ReportSummaryStatisticLog'),'IsUserTable'))
	begin print 'exist ReportSummaryStatisticLog'end
	else
	begin print ' not exist ReportSummaryStatisticLog'end

	-- 判断列是否存在
	-- this sql is wrong
	--if col_length('dbo.ReportSummaryStatisticLog','ReportKey') is not null
	-- begin print 'exist db field ReportKey' end
	--else
	-- begin print 'not exist db field ReportKey' end

	-- 判断列是否存在
	use HealthService
	go
	IF EXISTS(select a.Name as tableName from sysobjects a inner join syscolumns b on a.ID=b.ID
	where b.Name='ReportKey' -- COLUMN NAME
	AND A.name='ReportSummaryStatisticLog' -- TABLE_NAME
	)
	BEGIN PRINT 'EXIST COLUMN ReportKey' END
	ELSE
	BEGIN PRINT 'NOT EXIST COLUMN ReportKey' END

	
	--判断sql job 是否存在
	IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'CreateReportStatisticForAllUsers')
	BEGIN
	--EXEC msdb.dbo.sp_delete_job @job_name=N'CreateReportStatisticForAllUsers', @delete_unused_schedule=1
	print 'exist CreateReportStatisticForAllUsers'
	END
	ELSE
	BEGIN
	 print 'NOT exist CreateReportStatisticForAllUsers'
	END
	GO

	-- 立即执行sql job
	use msdb
	IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'CreateReportStatisticForAllUsers')
	begin
	--exec sp_start_job @job_name='CreateReportStatisticForAllUsers'
	print 'exist CreateReportStatisticForAllUsers'
	end
	ELSE
	BEGIN
	 print 'NOT exist CreateReportStatisticForAllUsers'
	END

	
	-- 游标 遍历某个数据库中所有的表
	BEGIN
	PRINT 1
	--DECLARE @Name nvarchar(255),@Sql nvarchar(4000)
	--DECLARE TName CURSOR FOR

	--SELECT [name] FROM sysobjects WHERE xtype = 'U'
	--OPEN TName
	--FETCH NEXT FROM TName INTO @Name
	--WHILE @@FETCH_STATUS = 0
	--BEGIN
	-- SET @Sql='SELECT TOP 2 * FROM ['+@name + '] WHERE 1=1
	-- -- 这里还可以拼其它你想要的过滤条件
	-- '
	-- EXEC(@Sql)
	-- FETCH NEXT FROM TName INTO @name
	--END

	--CLOSE TName
	--DEALLOCATE TName
	END
试试其它关键字
　sql　　工具函数　
同语言下
可能有用的
步帝贡献的其它代码(12)