常用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，游标，存储过程，事务

http://blog.csdn.net/keenweiwei/article/details/8637207

 

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