用户自定义函数

Question

SELECT COUNT(*) AS Expr1
FROM sysobjects
WHERE (xtype = 'FN')Expr1
--------
17
----------------------------------------------------
使用的是SqlServer2000的数据库
我想问的是：数据库内有自定义函数77个为什么在sysobjects表中只能找到17个？

Answer 1

--FN只是一部分，参考以下：
C = CHECK 约束 
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束 
FN = 标量函数
IF = 内嵌表函数
K = PRIMARY KEY 或 UNIQUE 约束 
L = 日志
P = 存储过程
R = 规则
RF = 复制筛选存储过程
S = 系统表 
TF = 表函数
TR = 触发器
U = 用户表
V = 视图
X = 扩展存储过程

Answer 2

另外还想问一点，
select * into aa from bb where 1=2 这个语句虽然可以复制表的结构但是没法复制它的主键和列的默认值，有没有什么其它的方法既可以复制表达结构又可以复制它的主键和列的默认值（不使用导出脚本的方式）
希望大家帮忙看看

Answer 3

--TRY
SELECT COUNT(*) AS Expr1
FROM sysobjects
WHERE xtype in('FN','IF')

Answer 4

SELECT COUNT(*) AS Expr1
FROM sysobjects
WHERE (xtype in( 'FN','TF'))
order by name

Answer 5

SELECT COUNT(*) AS Expr1
FROM sysobjects
WHERE (xtype = 'FN' or 'tf')

Answer 6

单列主键情况，复合情况你修改一下就行了
create table bb (id int primary key ,col int)
go
select * into aa from bb where 1 =2 
declare @s varchar(8000)
set @s = ''SELECT @s = 'ALTER TABLE [aa] with nocheck add constraint pk__aa primary key clustered('+
 c.name +')'
FROM sysobjects a,syscolumns c,sysindexkeys bwhere a.xtype='PK' and a.parent_obj = c.id and b.id = c.id and c.colid = b.colid and
      c.id = object_id('bb')
exec( @s)
go
sp_help 'aa'
/*
index_name                  index_description                             index_keys                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
--------- ------------------------------------- ----------------------------------------
pk__aa     clustered, unique, primary key located on PRIMARY                id*/
drop table aa,bb