sql 工具函数 (未完待续)

--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