常用sql脚本模板

1，建新表：

USE [YOUR_DB_NAME]
GO

IF EXISTS (SELECT * FROM SYSOBJECTS 
               WHERE [NAME] = 'YOUR_TABLE_NAME'
               AND XTYPE='U')  
BEGIN
   DROP TABLE [YOUR_TABLE_NAME]
END

GO


CREATE TABLE [dbo].[YOUR_TABLE_NAME](
   FIELD1 VARCHAR(20),
   ---
)
GO

2, 修改某个字段

USE [YOUR_DB_NAME]
GO

IF(
  EXISTS(
    SELECT TOP 1 1 FROM SYSCOLUMNS 
    WHERE ID = (
      SELECT ID FROM SYSOBJECTS 
      WHERE TYPE='U' AND NAME='YOUR_TABLE_NAME')
    AND NAME = 'YOUR_FIELD_NAME')
)
BEGIN
  --print 'exist'
  ALTER TABLE [dbo].[YOUR_TABLE_NAME] ALTER COLUMN [YOUR_FIELD_NAME] varchar(50) NULL
END

3，新建临时表

USE MASTER  
GO  

if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tmpResult'))  
begin  
    drop table #tmpResult  
end  

create table #tmpResult  
(  
    DbName varchar(100),  
    SpOrViewName varchar(100),  
    [Type] varchar(2),  
    Content varchar(100)  
)  
go

4，游标，存储过程，事务

5，判断是否存在函数

-- 判断是否存在函数
-- 只能判断是否存在 标量值函数
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE XTYPE='FN' AND NAME='fn_GetDayCountOfPreviousMonth')
  PRINT 1
ELSE 
  PRINT 0


-- 判断是否存在函数
-- 可以判断是否存在 标量值函数 或 表值函数
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[fn_GetDayCountOfPreviousMonth]') AND XTYPE IN (N'FN', N'IF', N'TF'))
  PRINT 1
ELSE 
  PRINT 0