SQL查表名、字段名、表说明、字段说明
SQL 查看所有表名:
select name from sysobjects where type='U'
查询表的所有字段名:
Select name from syscolumns Where ID=OBJECT_ID('表名')
select * from information_schema.tables
select * from information_schema.views
select * from information_schema.columns
ACCESS
查看所有表名:
select name from MSysObjects where type=1 and flags=0
MSysObjects是系统对象,默认情况是隐藏的。通过工具、选项、视图、显示、系统对象可以使之显示出来。
SQL查询表的备注说明
SELECT 表名 = case when a.colorder = 1 then d.name
else '' end,
表说明 = case when a.colorder = 1 then isnull(f.value, '')
else '' end
from syscolumns a
inner join sysobjects d
on a.id = d.id
and d.xtype = 'U'
and d.name <> 'sys.extended_properties'
left join sys.extended_properties f
on a.id = f.major_id
and f.minor_id = 0
SQL查询表的所有字段的备注说明
SQL_1:
SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,
systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,
sys.extended_properties.[value] AS COLUMN_DESCRIPTION, syscomments.text as
COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE from syscolumns
INNER JOIN systypes
ON syscolumns.xtype = systypes.xtype
LEFT JOIN sysobjects ON syscolumns.id = syso
相关文档:
转自:http://hi.baidu.com/cszoo/blog/item/2439a5f517c19c2dbc31093c.html
(1)
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,' ......
第一步, 在收缩前先查看日志的大小:
SELECT *
from sysfiles
WHERE name LIKE ' % LOG %'
GO
第二步, 把数据库的恢复模式设成”简单”:
ALTER DATABASE 库名 SET RECOVERY SIMPLE
GO
第三步, 运行checkpoint指令, 把dirty page写进数据库:
CHECKPOINT
GO
第四步, 截断日志:
BACKUP ......
-- 示例一, 使用证书加密数据.
-- 建立测试数据表
CREATE TABLE tb(ID int IDENTITY (1,1),data varbinary (8000));
GO
-- 建立证书一, 该证书使用数据库主密钥来加密
CREATE CERTIFICATE Cert_Demo1
WITH
SUBJECT = N'cert1 encryption by database master key' ,
START_DATE = ......
今天做SQL 优化,查找执行计划时,执行计划,发现此执行计划与以往的计划有所区别;找录互联网,终于找一篇有关研究比较深入的文章;
原执行计划使用的是索引扫描,突然一下会使用索引覆盖技术,效率大增;
SELECT * 的真相:索引覆盖(index coverage)
SELECT *的效率很糟糕吗?当然,所有人都知道这一点,但是为什么 ......