select a.name,a.length,b.name from syscolumns a , systypes b where a.xtype=b.xtype and a.id=object_id('tablename')
select * from sysobjects where type='U' go select a.name,b.name,a.length from syscolumns a,systypes b where a.xtype=b.xtype and a.id=object_id('yourtable') go
use test01SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'int' 可以选出一个库里的。 数据库名和字段类型自己改,另外可能会选出系统表里的,没有仔细测试。
use test01SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS, sysobjects where DATA_TYPE = 'int' and INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = sysobjects.name and sysobjects.xtype = 'u'除了表dtproperties,系统表也能滤掉了。
我刚才试了一下,这样可以的: select a.name,a.length,b.name from syscolumns a, systypes b,sysobjects c where a.xtype=b.xtype and a.id=c.id and c.name='tjxzbbm' ,你可以试试。
这个问题应该用不着游标。 select distinct b.name from syscolumns a, systypes b,sysobjects c where a.xtype=b.xtype and a.id=c.id and c.type='U'
select distinct data_type from information_schema.columns, sysobjects where information_schema.columns.table_name = sysobjects.name and sysobjects.type = 'u'
information_schema这个东西在那可以看到,都有些什么功能呢?
information_schema是什么东西,都有些什么功能呢?能给些资料吗?
select c.Name as TableName,b.name as ColName,a.name as Type,a.length from syscolumns a,systypes b,sysobjects c where c.type='U' a.xtype=b.xtype and a.id=c.id
更正:select c.Name as TableName,b.name as ColName,a.name as Type,a.length from syscolumns a,systypes b,sysobjects c where c.type='U' and a.xtype=b.xtype and a.id=c.id
select distinct b.name from syscolumns a, systypes b,sysobjects c where a.xtype=b.xtype and a.id=c.id and c.type='U'试试,没问题的
go
select a.name,b.name,a.length from syscolumns a,systypes b where
a.xtype=b.xtype and a.id=object_id('yourtable')
go
FROM INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE = 'int' 可以选出一个库里的。
数据库名和字段类型自己改,另外可能会选出系统表里的,没有仔细测试。
FROM INFORMATION_SCHEMA.COLUMNS, sysobjects
where DATA_TYPE = 'int'
and INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = sysobjects.name
and sysobjects.xtype = 'u'除了表dtproperties,系统表也能滤掉了。
select a.name,a.length,b.name from syscolumns a, systypes b,sysobjects c
where a.xtype=b.xtype and a.id=c.id and c.name='tjxzbbm'
,你可以试试。
select distinct b.name from syscolumns a, systypes b,sysobjects c
where a.xtype=b.xtype and a.id=c.id and c.type='U'
from information_schema.columns, sysobjects
where information_schema.columns.table_name = sysobjects.name
and sysobjects.type = 'u'
a.xtype=b.xtype and a.id=c.id
from syscolumns a,systypes b,sysobjects
c where c.type='U' and
a.xtype=b.xtype and a.id=c.id
where a.xtype=b.xtype and a.id=c.id and c.type='U'试试,没问题的