本人写的数据库常用函数（存储过程中经常要用到）
GO/****** 对象: UserDefinedFunction [dbo].[fun_get_LowerFirst] 脚本日期: 08/04/2012 13:03:56 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_get_comment]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[fun_get_comment]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ON/******************************************************* DECRIPTION: 获取表中字段的描述（说明）** VERSION AUTH DATE Defect NoDESC** -------- ------------ ------------ ----------------- ------------------------------** V000.0.1 pukuimin 08/04/2012新建程序** -------- ------------ ------------ ----------------- -------------------------------*******************************************************/GOcreate function [dbo].[fun_get_comment](@tablename varchar(200), ----表名@fieldname varchar(200) ----字段名 )returns varchar(200)asbegindeclare @returnstr varchar(200)select distinct @returnstr =cast(b.value as varchar(200))from syscolumns a left outer join sys.extended_properties b on a.id=b.major_id and a.colid=b.minor_id where a.name=@fieldname and a.id=object_id(@tablename)if isnull(@returnstr,'')=''beginset @returnstr=''endreturn @returnstrend/*REF_SEQT_PO_REQ_ORDR_LINEselect [dbo].[fun_get_comment]('stuinfo','username')*/GO/****** 对象: UserDefinedFunction [dbo].[fun_get_UpperFirst] 脚本日期: 08/03/2012 10:10:07 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_get_UpperFirst]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[fun_get_UpperFirst]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ON/******************************************************* DECRIPTION: 将字符串首字母大写** VERSION AUTH DATE Defect NoDESC** -------- ------------ ------------ ----------------- ------------------------------** V000.0.1 pukuimin 08/03/2012新建程序** -------- ------------ ------------ ----------------- -------------------------------*******************************************************/GOcreate function [dbo].[fun_get_UpperFirst]( @letters varchar(200) )returns varchar(200)asbegindeclare @returnstr varchar(200)if isnull(@letters,'')='' set @returnstr=''elseset @returnstr=Upper(Substring(@letters,1,1))+Substring(@letters,2,len(@letters)-1)return @returnstrendGO/****** 对象: UserDefinedFunction [dbo].[fun_get_LowerFirst] 脚本日期: 08/03/2012 10:10:33 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_get_LowerFirst]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[fun_get_LowerFirst]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ON/******************************************************* DECRIPTION: 字符串首字母小写** VERSION AUTH DATE Defect NoDESC** -------- ------------ ------------ ----------------- ------------------------------** V000.0.1 pukuimin 08/04/2012新建程序** -------- ------------ ------------ ----------------- -------------------------------*******************************************************/GOcreate function [dbo].[fun_get_LowerFirst]( @letters varchar(200) )returns varchar(200)asbegindeclare @returnstr varchar(200)if isnull(@letters,'')='' set @returnstr=''elseset @returnstr=Lower(Substring(@letters,1,1))+Substring(@letters,2,len(@letters)-1)return @returnstrendGO/****** 对象: UserDefinedFunction [dbo].[fun_get_PrimaryKey] 脚本日期: 08/04/2012 14:41:32 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_get_PrimaryKey]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[fun_get_PrimaryKey]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/******************************************************* PROCEDURE : [pro_GenerateProSet]** DECRIPTION: 获取表的主键（多个以“,”分开）** VERSION AUTH DATE Defect NoDESC** -------- ------------ ------------ ----------------- ------------------------------** V000.0.1 pukuimin 08/04/2012新建程序** -------- ------------ ------------ ----------------- -------------------------------*******************************************************/create function [dbo].[fun_get_PrimaryKey](@tablename varchar(200) ----表名 )returns varchar(200)asbegindeclare @returnstr varchar(200)set @returnstr=''--select distinct @returnstr =cast(b.value as varchar(200))SELECT @returnstr=@returnstr+CCU.COLUMN_NAME+','FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAMEWHERE TC.TABLE_NAME = @tablename AND TC.CONSTRAINT_TYPE='PRIMARY KEY'set @returnstr=LEFT(@returnstr,LEN(@returnstr)-1)if isnull(@returnstr,'')=''beginset @returnstr=''endreturn @returnstrend/*select [dbo].[fun_get_PrimaryKey]('stuinfo')*/GO/****** 对象: UserDefinedFunction [dbo].[fun_get_unique_column] 脚本日期: 08/04/2012 14:41:32 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_get_unique_column]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[fun_get_unique_column]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/******************************************************* DECRIPTION: 获取表的自动增长列** VERSION AUTH DATE Defect NoDESC** -------- ------------ ------------ ----------------- ------------------------------** V000.0.1 pukuimin 08/04/2012新建程序** -------- ------------ ------------ ----------------- -------------------------------*******************************************************/create function [dbo].[fun_get_unique_column](@tablename varchar(200) ----表名 )returns varchar(200)asbegindeclare @returnstr varchar(200)set @returnstr=''select top 1 @returnstr = a.Namefrom syscolumns a left join sysobjects b on a.iD=b.parent_obj and b.xtype='PK'where a.ID=object_id(@tablename) and a.status=0x80if isnull(@returnstr,'')=''beginset @returnstr=''endreturn @returnstrend/*select [dbo].[fun_get_unique_column]('stuinfo')*/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_get_tabspace]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[fun_get_tabspace]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ON/******************************************************* DECRIPTION: 得到Tab空格个数 4*n个空格** VERSION AUTH DATE Defect NoDESC** -------- ------------ ------------ ----------------- ------------------------------** V000.0.1 pukuimin 08/03/2012新建程序** -------- ------------ ------------ ----------------- -------------------------------*******************************************************/GOcreate function [dbo].[fun_get_tabspace](@TabCount int ----Tab个数)returns varchar(200)asbegindeclare @returnstr varchar(200)declare @mycount intset @mycount=isnull(@TabCount,0)set @returnstr=''while (@mycount>0)beginset @mycount=@mycount-1set @returnstr=@returnstr+' 'endreturn @returnstrend/*select [dbo].[fun_get_tabspace](3) as test*/GO/****** 对象: UserDefinedFunction [dbo].[fun_get_column_length] 脚本日期: 08/04/2012 14:15:10 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_get_column_length]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[fun_get_column_length]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/******************************************************* DECRIPTION: 获取表中某字段定义的长度(带“（）”)** VERSION AUTH DATE Defect NoDESC** -------- ------------ ------------ ----------------- ------------------------------** V000.0.1 pukuimin 08/04/2012新建程序** -------- ------------ ------------ ----------------- -------------------------------*/ create function [dbo].[fun_get_column_length](@tablename varchar(200), ----表名@fieldname varchar(200) ----字段名 )returns varchar(200)asbegindeclare @returnstr varchar(200)select @returnstr=cast( CASE WHEN DATA_TYPE='NVARCHAR' OR DATA_TYPE='VARCHAR' OR DATA_TYPE='CHAR'OR DATA_TYPE='NCHAR' THEN '('+CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(20))+')'when data_type='numeric' then '('+CAST(numeric_precision AS NVARCHAR(20))+','+CAST(numeric_scale AS NVARCHAR(20))+')' ELSE '' ENDas varchar(200))FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = @tablename and COLUMN_NAME=@fieldname if isnull(@returnstr,'')=''beginset @returnstr=''endreturn @returnstrend/*select [dbo].[fun_get_column_length]('stuinfo','id')*/GO/****** 对象: UserDefinedFunction [dbo].[fun_get_column_str_length] 脚本日期: 08/04/2012 14:15:10 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_get_column_str_length]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[fun_get_column_str_length]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/******************************************************* DECRIPTION: 获取表中某—字符类型/numeric—字段的长度** VERSION AUTH DATE Defect NoDESC** -------- ------------ ------------ ----------------- ------------------------------** V000.0.1 pukuimin 08/04/2012新建程序** -------- ------------ ------------ ----------------- -------------------------------*/ create function [dbo].[fun_get_column_str_length](@tablename varchar(200), ----表名@fieldname varchar(200) ----字段名 )returns varchar(200)asbegindeclare @returnstr varchar(200)select @returnstr=cast( CASE WHEN DATA_TYPE='NVARCHAR' OR DATA_TYPE='VARCHAR' OR DATA_TYPE='CHAR'OR DATA_TYPE='NCHAR' THEN CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(20))when data_type='numeric' then CAST(numeric_precision AS NVARCHAR(20)) ELSE '' ENDas varchar(200))FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = @tablename and COLUMN_NAME=@fieldname if isnull(@returnstr,'')=''beginset @returnstr=''endreturn @returnstrend/*select [dbo].[fun_get_column_str_length]('stuinfo','id')*/GO/****** 对象: UserDefinedFunction [dbo].[fun_get_param_length] 脚本日期: 08/04/2012 14:15:10 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_get_param_length]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[fun_get_param_length]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/******************************************************* DECRIPTION: 获取存储过程中参数字符串类型/numeric字段的长度** VERSION AUTH DATE Defect NoDESC** -------- ------------ ------------ ----------------- ------------------------------** V000.0.1 pukuimin 08/04/2012新建程序** -------- ------------ ------------ ----------------- -------------------------------*/ create function [dbo].[fun_get_param_length](@ProName varchar(200), ----存储过程名@fieldname varchar(200) ----字段名 )returns varchar(200)asbegindeclare @returnstr varchar(200) select @returnstr=cast( CASE WHEN type_name(user_type_id)='NVARCHAR' OR type_name(user_type_id)='VARCHAR' OR type_name(user_type_id)='CHAR'OR type_name(user_type_id)='NCHAR' THEN CAST(max_length AS NVARCHAR(20))when type_name(user_type_id)='numeric' then CAST(case when type_name(system_type_id) = 'uniqueidentifier' then precision else OdbcPrec(system_type_id, max_length, precision) end AS NVARCHAR(20)) ELSE '' ENDas varchar(200))from sys.all_parameters where object_id = object_id(@ProName) and [name]=@fieldnameif isnull(@returnstr,'')=''beginset @returnstr=''endreturn @returnstrend/*select [dbo].[fun_get_param_length]('pro_set_Stuinfo','@score')*/