本人写的数据库常用函数（存储过程中经常要用到）

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 ON
GO
SET QUOTED_IDENTIFIER ON
/*****************************************************
** DECRIPTION: 获取表中字段的描述（说明）
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** V000.0.1    pukuimin     08/04/2012							新建程序						
** --------  ------------  ------------  -----------------   -------------------------------
*******************************************************/
GO
create function [dbo].[fun_get_comment](
							@tablename varchar(200),  ----表名
							@fieldname varchar(200)  ----字段名
								 )
returns varchar(200)
as
begin	
declare @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,'')=''
	begin
	set @returnstr=''
	end
return @returnstr
end

/*

REF_SEQ

T_PO_REQ_ORDR_LINE


select [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 ON
GO
SET QUOTED_IDENTIFIER ON
/*****************************************************
** DECRIPTION: 将字符串首字母大写
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** V000.0.1    pukuimin     08/03/2012							新建程序						
** --------  ------------  ------------  -----------------   -------------------------------
*******************************************************/
GO
create function [dbo].[fun_get_UpperFirst](  @letters varchar(200)
								 )
returns varchar(200)
as
begin
declare @returnstr varchar(200)

if isnull(@letters,'')='' set @returnstr=''
else
set @returnstr=Upper(Substring(@letters,1,1))+Substring(@letters,2,len(@letters)-1)
return @returnstr
end


GO
/****** 对象:  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 ON
GO
SET QUOTED_IDENTIFIER ON
/*****************************************************
** DECRIPTION: 字符串首字母小写
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** V000.0.1    pukuimin     08/04/2012							新建程序						
** --------  ------------  ------------  -----------------   -------------------------------
*******************************************************/
GO
create function [dbo].[fun_get_LowerFirst](  @letters varchar(200)
								 )
returns varchar(200)
as
begin	
declare @returnstr varchar(200)
if isnull(@letters,'')='' set @returnstr=''
else
set @returnstr=Lower(Substring(@letters,1,1))+Substring(@letters,2,len(@letters)-1)
return @returnstr
end


GO
/****** 对象:  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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************
** PROCEDURE : [pro_GenerateProSet]
** DECRIPTION: 获取表的主键（多个以“,”分开）
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** V000.0.1    pukuimin     08/04/2012							新建程序						
** --------  ------------  ------------  -----------------   -------------------------------
*******************************************************/

create function [dbo].[fun_get_PrimaryKey](
							@tablename varchar(200)  ----表名
								 )
returns varchar(200)
as
begin	
declare @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_NAME
WHERE TC.TABLE_NAME = @tablename AND TC.CONSTRAINT_TYPE='PRIMARY KEY'
set @returnstr=LEFT(@returnstr,LEN(@returnstr)-1)
if isnull(@returnstr,'')=''
	begin
	set @returnstr=''
	end
return @returnstr
end

/*

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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************
** DECRIPTION: 获取表的自动增长列
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** V000.0.1    pukuimin     08/04/2012							新建程序						
** --------  ------------  ------------  -----------------   -------------------------------
*******************************************************/
create function [dbo].[fun_get_unique_column](
							@tablename varchar(200)  ----表名
								 )
returns varchar(200)
as
begin	
declare @returnstr varchar(200)
set @returnstr=''

select top 1 @returnstr = a.Name
from 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=0x80

if isnull(@returnstr,'')=''
	begin
	set @returnstr=''
	end
return @returnstr
end

/*

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 ON
GO
SET QUOTED_IDENTIFIER ON
/*****************************************************
** DECRIPTION: 得到Tab空格个数  4*n个空格
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** V000.0.1    pukuimin     08/03/2012							新建程序						
** --------  ------------  ------------  -----------------   -------------------------------
*******************************************************/
GO
create function [dbo].[fun_get_tabspace](
@TabCount int ----Tab个数
)
returns varchar(200)
as

begin
declare @returnstr varchar(200)
declare @mycount int
	set @mycount=isnull(@TabCount,0)
	set @returnstr=''
	while (@mycount>0)
		begin
			set @mycount=@mycount-1
			set @returnstr=@returnstr+'    '
		end
	return @returnstr
end

/*
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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************
** DECRIPTION: 获取表中某字段定义的长度(带“（）”)
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** V000.0.1    pukuimin     08/04/2012							新建程序						
** --------  ------------  ------------  -----------------   -------------------------------
*/

 create function [dbo].[fun_get_column_length](
							@tablename varchar(200),  ----表名
							@fieldname varchar(200)  ----字段名
								 )
returns varchar(200)
as
begin	
declare @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
        ''
    END
as varchar(200)
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename and COLUMN_NAME=@fieldname
 
if isnull(@returnstr,'')=''
	begin
	set @returnstr=''
	end
return @returnstr
end

/*
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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************
** DECRIPTION: 获取表中某—字符类型/numeric—字段的长度
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** V000.0.1    pukuimin     08/04/2012							新建程序						
** --------  ------------  ------------  -----------------   -------------------------------
*/

 create function [dbo].[fun_get_column_str_length](
							@tablename varchar(200),  ----表名
							@fieldname varchar(200)  ----字段名
								 )
returns varchar(200)
as
begin	
declare @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
        ''
    END
as varchar(200)
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename and COLUMN_NAME=@fieldname
 
if isnull(@returnstr,'')=''
	begin
	set @returnstr=''
	end
return @returnstr
end

/*

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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************
** DECRIPTION: 获取存储过程中参数字符串类型/numeric字段的长度
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** V000.0.1    pukuimin     08/04/2012							新建程序						
** --------  ------------  ------------  -----------------   -------------------------------
*/

 create function [dbo].[fun_get_param_length](
							@ProName varchar(200),  ----存储过程名
							@fieldname varchar(200)  ----字段名
								 )
returns varchar(200)
as
begin	
declare @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
        ''
    END
as varchar(200)
)
from sys.all_parameters where object_id = object_id(@ProName) and [name]=@fieldname

if isnull(@returnstr,'')=''
	begin
	set @returnstr=''
	end
return @returnstr
end

/*

select [dbo].[fun_get_param_length]('pro_set_Stuinfo','@score')

*/