常用Sql server 自定义函数

/****** 对象: 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
GO
/*

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

*/