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), ----表名
&nb