1. 获取所有数据库名 : Select Name FROM Master..SysDatabases 2. 获取所有表名 : select name from sysobjects where type='U' XType='U': 表示所有用户表 ; XType='S': 表示所有系统表 ; 3. 获取所有字段名 : Select Name from SysColumns Where id=Objec
1.获取所有数据库名:
Select Name FROM Master..SysDatabases
2.获取所有表名:
select name from sysobjects where type='U'
XType='U':表示所有用户表;
XType='S':表示所有系统表;
3.获取所有字段名:
Select Name from SysColumns Where id=Object_Id('TableName')
获取当前数据库表名:
select name from sysobjects where xtype='U'and name<>'dtproperties'
获取当前表的字段名及属性:
select a.name, b.xtype,b.name
from syscolumns a
inner JOIN systypes b
ON a.xtype=b.xusertype
inner join sysobjects c ON
a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties' where c.name =表名
或:
SELECT
表名= CASE a.colorder WHEN 1 THEN c.name ELSE '' END,
序= a.colorder,
字段名= a.name,
标识= CASE COLUMNPROPERTY(a.id,a.name,'IsIdentity') WHEN 1 THEN '√' ELSE '' END,
主键= CASE
WHEN EXISTS (
SELECT *
FROM sysobjects
WHERE xtype='PK' AND name IN (
SELECT name
FROM sysindexes
WHERE id=a.id AND indid IN (
SELECT indid
FROM sysindexkeys
WHERE id=a.id AND colid IN (
SELECT colid
FROM syscolumns
WHERE id=a.id AND name=a.name
)
)
)
)
THEN '√'
ELSE ''
END,
类型= b.name,
字节数= a.length,
长度= COLUMNPROPERTY(a.id,a.name,'Precision'),
小数= CASE ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)
WHEN 0 THEN ''
ELSE CAST(COLUMNPROPERTY(a.id,a.name,'Scale') AS VARCHAR)
END,
允许空= CASE a.isnullable WHEN 1 THEN '√' ELSE '' END,
默认值= ISNULL(d.[text],''),
说明= ISNULL(e.[value],'')
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype=b.xusertype
INNER JOIN sysobjects c ON a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties'
LEFT JOIN syscomments d ON a.cdefault=d.id
LEFT JOIN sysproperties e ON a.id=e.id AND a.colid=e.smallid
ORDER BY c.name, a.colorder
回答者:alby -魔法师 五级2006-7-26 11:51
我找到并在ACCESS里测试通过了:
在ACCESS里,备注类型用Memo表示,所以改变字段的数据类型为备注的SQL为:
ALTER TABLE user ALTER COLUMN userinfo Memo
对了,如果user表有外键,而且你要修改的字段就是外键的话,你就不能修改啦!
下面给出修改为其它类型的SQL(表为tb,字段为aa):
ALTER TABLE tb ALTER COLUMN aa Byte数字[字节]
ALTER TABLE tb ALTER COLUMN aa Long数字[长整型]
ALTER TABLE tb ALTER COLUMN aa Short数字[整型]
ALTER TABLE tb ALTER COLUMN aa Single数字[单精度
ALTER TABLE tb ALTER COLUMN aa Double数字[双精度]
ALTER TABLE tb ALTER COLUMN aa Currency货币
ALTER TABLE tb ALTER COLUMN aa Char文本
ALTER TABLE tb ALTER COLUMN aa Text(n)文本,其中n表示字段大小
ALTER TABLE tb ALTER COLUMN aa Binary二进制
ALTER TABLE tb ALTER COLUMN aa Counter自动编号
ALTER TABLE tb ALTER COLUMN aa Memo备注
ALTER TABLE tb ALTER COLUMN aa Time日期/时间
本文原创发布php中文网,转载请注明出处,感谢您的尊重!