我咋看不懂问的是些what呢?麻烦说清楚些好么?
解决方案 »
- ADOQUERY缓存更新如何设置?
- 发现Dephi写底层的东西力不从心
- 高分求rxlib for delphi6控件(特急)
- 如何给软件定价,征集思路
- delphi的属性问题
- 请教一个简单的问题,怎样取得一个文件的三个时间
- 关于两个打印机同时工作的问题
- 请问哪里有播放rm视频的控件下载?
- 高手请进!疯狂大送分了!关于数据过滤的问题
- ASP中用VBSCRIPT这样写SET RS=Server.CreateObject("ADODB.recordset")在DELPHI中怎样来写一个语句完成同样的功能?RS是创建的一个对象……
- PostThreadMessage问题,知之者请入,不知者请看
- ehlib控件的问题,如何在表中的一字段显示一个列表?分可以加,,,在线等
作个朋友吧,QQ我挂在网上的时间比较少。
我一般隔一段时间才到这里来看看,有时可能不会太及时回。
字段的ID我确实不太清楚了,没有找到。
但是有字段序号的。有了表 的id 和字段的colid应该可以了吧。
要删除索引
drop index tablename.indexname 就可以了。
主键索引:alter table [tablename] DROP CONSTRAINT [PRIMARY KEY NAME]
其实MSSQL的帮助还是挺全,也比较好找。多看看就好了。
ID字段?是指什么。identity?
alter table [tablename] DROP CONSTRAINT [PRIMARY KEY NAME]这个[PRIMARY KEY NAME]中的 NAME是什么。字段名吗?好象也是错的。
我有2个主键呢。我现在又遇到这样的问题了,就是怎么样修改缺省值。
我自己也会看帮助的,因为时间比较急
也就是:
select name
from sysobjects o
where o.xtype = 'pk'
给个例子:
alter table dtproperties drop CONSTRAINT pk_dtproperties缺省值不能用Alter table来直接改,先创建一个缺省值,再绑定。
呵呵,全都是帮助里的例子。
创建
CREATE DEFAULT phonedflt AS 'unknown'
sp_bindefault phonedflt, 'authors.phone'
删除
EXEC sp_unbindefault 'authors.phone'
DROP DEFAULT phonedflt你在做自己的数据库维护程序啊,这些东西平时不怎么用的到。
也就是:
select name
from sysobjects o
where o.xtype = 'pk'
给个例子:
alter table dtproperties drop CONSTRAINT pk_dtproperties缺省值不能用Alter table来直接改,先创建一个缺省值,再绑定。
呵呵,全都是帮助里的例子。
创建
CREATE DEFAULT phonedflt AS 'unknown'
sp_bindefault phonedflt, 'authors.phone'
删除
EXEC sp_unbindefault 'authors.phone'
DROP DEFAULT phonedflt你在做自己的数据库维护程序啊,这些东西平时不怎么用的到。
比如我在ADOQUERY里写,SELECT * FROM #TEMP 就出错的
(SELECT c.text
FROM syscomments c
WHERE a.cdefault = c.id) AS 'DEFAULTSTR',
(SELECT column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
WHERE EXISTS
(SELECT s.id
FROM sysobjects s
WHERE s.xtype = 'pk' AND s.name = k.constraint_name) AND
table_name = b.name AND column_name = a.name) AS keystr,
(SELECT c.name
FROM syscolumns c, sysobjects o
WHERE NOT c.autoval IS NULL AND o.id = c.id AND c.name = a.name AND
o.name = b.name) AS idstr
FROM syscolumns a, sysobjects b
WHERE a.id = b.id AND b.type = 'u' AND a.xtype = a.xusertype AND a.xtype <> 213 AND
b.name NOT BETWEEN 'sysmer' AND 'sysmerz' AND b.name NOT BETWEEN
'MSmerge_' AND 'MSmerge_z'
或者你建立视图吧,这样最后的Select写着简单,性能也会好些。我把你的语句稍微改了一点,建立了两个视图,我这里测试性能好了些。不知道改的是否符合你的需要,你检验一下看看再说吧。create view view_test as
SELECT column_name,TABLE_NAME
--INTO #TEMP
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k,syscolumns a, sysobjects b
WHERE EXISTS
(SELECT s.id
FROM sysobjects s
WHERE s.xtype = 'pk' AND s.name = k.constraint_name) AND
table_name = b.name AND column_name = a.name
AND a.id = b.id AND b.type = 'u' AND a.xtype = a.xusertype AND a.xtype <> 213 AND
b.name NOT BETWEEN 'sysmer' AND 'sysmerz' AND b.name NOT BETWEEN
'MSmerge_' AND 'MSmerge_z' create view view_test2 as
SELECT b.name AS tablename, a.name AS fieldname,a.xtype, a.length, a.isnullable,
-- c.text as 'DEFAULTSTR',
(SELECT c.text
FROM syscomments c
WHERE a.cdefault = c.id) AS 'DEFAULTSTR',
/*
(SELECT column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
WHERE
EXISTS
(SELECT s.id
FROM sysobjects s
WHERE s.xtype = 'pk' AND s.name = k.constraint_name) AND */
--(select column_name from view_test where
-- table_name = b.name AND column_name = a.name) AS keystr,
/*(SELECT c.name
FROM syscolumns c, sysobjects o
WHERE NOT c.autoval IS NULL AND o.id = c.id AND c.name = a.name AND
o.name = b.name) AS idstr
*/
case when a.autoval is null then null else a.name end AS idstr
FROM syscolumns a, sysobjects b --,view_test v
WHERE a.id = b.id AND b.type = 'u' AND a.xtype = a.xusertype AND a.xtype <> 213 AND
b.name NOT BETWEEN 'sysmer' AND 'sysmerz' AND b.name NOT BETWEEN
'MSmerge_' AND 'MSmerge_z' select v2.* , v.column_name AS keyst
from view_test2 v2 left outer join view_test v on v2.tablename = v.table_name and v2.fieldname = v.column_name
SELECT c.text
FROM syscomments c
WHERE a.cdefault = c.id) AS 'DEFAULTSTR', 我想把这个DEFAULSTR 和最下面的WHERE 连起来,也就是我想查DEFAULTSTR这个值为多少的记录应该怎么写呢。比如DEFALUTSTR=‘1’的记录,不能用临时表
不知道为什么这样慢,在MSSQL上直接运行的话,4,5秒就够了,而在DELPHI的ADOQUERY 中竟然这么慢。可能我的机器比较差
不太明白你的意思。是要另外的一个select语句吗,还是想要在这个语句的结果集上进行过滤操作。
我以前还真没有在Adoquery里用过临时表,刚刚试了一下,可以用啊,下边是我的SQL在WIN2k Server+ D6 Update pack2 + MSSQL2K通过并且结果正确。我暂时没有MSSQL7.0,明天再试吧。
SELECT b.name AS tablename, a.name AS fieldname,a.xtype, a.length, a.isnullable,
(SELECT c.text
FROM syscomments c
WHERE a.cdefault = c.id) AS DEFAULTSTR,
(SELECT column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
WHERE EXISTS
(SELECT s.id
FROM sysobjects s
WHERE s.xtype = 'pk' AND s.name = k.constraint_name) AND
table_name = b.name AND column_name = a.name) AS keystr,
(SELECT c.name
FROM syscolumns c, sysobjects o
WHERE NOT c.autoval IS NULL AND o.id = c.id AND c.name = a.name AND
o.name = b.name) AS idstr
into #temp
FROM syscolumns a, sysobjects b
WHERE a.id = b.id AND b.type = 'u' AND a.xtype = a.xusertype AND a.xtype <> 213 AND
b.name NOT BETWEEN 'sysmer' AND 'sysmerz' AND b.name NOT BETWEEN
'MSmerge_' AND 'MSmerge_z'select *
from #temp
where DEFAULTSTR = '(0)'
drop table #temp
我没有明白你的意思,为什么不能用?这个数据集不需要更新操作,不用临时表也需要刷新那,这样刷新速度还好不少。