韦德体育数据库字典主要包括表结构(分为SQL Server,今天抽出点时间把我常用的sqlserver的一些东西整理了下

  • 栏目:数据 时间:2020-03-31 17:17
<返回列表

平时工作一会搞Oracle一会搞SqlServer,脑子都迷糊了,为了避免继续模糊,今天抽出点时间把我常用的sqlserver的一些东西整理了下,发出来,以后忘了就来找,过几天再整理个Oracle常用知识。没啥技术含量主要是备忘。

本文主要介绍了三个实用的SQL Server数据库字典SQL语句,数据库字典主要包括表结构(分为SQL Server 2000和SQL Server 2005)、索引和主键. 外键.约束.视图.函数.存储过程.触发器。你可以在查询分析器、企业治理器中简单执行后,快速的查出SQL Server 2000及SQL Server 2005的全部数据字典。

1.SQLserver忘记密码修改方法

1. SQL Server 2000数据库字典(表结构.sql)

从查询分析器中以Windows身份验证连接SQL Server 执行如下过程

SELECT TOP 100 PERCENT --a.id, 

CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名, 
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明, 
a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id, 
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识, 
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键, 
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION') 
AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, 
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 答应空, ISNULL(e.text, '') 
AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间, 
CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间
FROM dbo.syscolumns a LEFT OUTER JOIN
dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND 
d.status >= 0 LEFT OUTER JOIN
dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND 
g.name = 'MS_Description' LEFT OUTER JOIN
dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND 
f.name = 'MS_Description'
ORDER BY d.name, a.colorder

EXEC sp_password NULL, ''你的新密码'', ''用户名例如sa''

◆SQL Server 2005数据库字典(表结构.sql)

2.更改当前数据库中对象的所有者。

SELECT TOP 100 PERCENT --a.id,
CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,
a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,
CASE WHEN EXISTS
(SELECT 1
韦德体育 ,FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')
AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 答应空, ISNULL(e.text, '')
AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,
CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间
FROM dbo.syscolumns a LEFT OUTER JOIN
dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND
d.status >= 0 LEFT OUTER JOIN
dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties g ON a.id = g.major_id AND a.colid = g.major_id AND
g.name = 'MS_Description' LEFT OUTER JOIN
sys.extended_properties f ON d.id = f.major_id AND f.major_id = 0 AND

f.name = 'MS_Description'
ORDER BY d.name, a.colorder

EXEC sp_changeobjectowner ''dbo.对象名例如表名'', ''新所有者''

2.SQL Server数据库字典(索引.sql)

3.SQLSERVER与SQLSERVER之间的分布式查询

SELECT TOP 100 PERCENT --a.id, 
      CASE WHEN b.keyno = 1 THEN c.name ELSE '' END AS 表名, 
      CASE WHEN b.keyno = 1 THEN a.name ELSE '' END AS 索引名称, d.name AS 列名, 
      b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending') 
      WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序, CASE WHEN p.id IS NULL 
      THEN '' ELSE '√' END AS 主键, CASE INDEXPROPERTY(c.id, a.name, 'IsClustered') 
      WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集, CASE INDEXPROPERTY(c.id, 
      a.name, 'IsUnique') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 唯一, 
      CASE WHEN e.id IS NULL THEN '' ELSE '√' END AS 唯一约束, 
      a.OrigFillFactor AS 填充因子, c.crdate AS 创建时间, c.refdate AS 更改时间
FROM dbo.sysindexes a INNER JOIN
      dbo.sysindexkeys b ON a.id = b.id AND a.indid = b.indid INNER JOIN
      dbo.syscolumns d ON b.id = d.id AND b.colid = d.colid INNER JOIN
      dbo.sysobjects c ON a.id = c.id AND c.xtype = 'U' LEFT OUTER JOIN
      dbo.sysobjects e ON e.name = a.name AND e.xtype = 'UQ' LEFT OUTER JOIN
      dbo.sysobjects p ON p.name = a.name AND p.xtype = 'PK'
WHERE (OBJECTPROPERTY(a.id, N'IsUserTable') = 1) AND (OBJECTPROPERTY(a.id, 
      N'IsMSShipped') = 0) AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0)
ORDER BY c.name, a.name, b.keyno

建立连接服务器exec sp_addlinkedserver ''TESTLINK'','''',''SQLOLEDB'',''远程数据库的ip地址''

创建链接服务器上远程登录之间的映射exec sp_addlinkedsrvlogin ''TESTLINK'',''false'',null,''SA'',''密码''

查询示例select * from TESTLINK.库名.dbo.表名

4.查看库中全部的表

CREATE VIEW dbo.ALL_TABLESASSELECT top 100 PERCENT a.ID, CASE WHEN a.colorder = 1 THEN d.name ELSE '''' END AS TableName, CASE WHEN a.colorder = 1 THEN isnull(f.value, '''') ELSE '''' END AS 表说明, a.colorder AS 字段序号, a.name AS 字段名, ISNULL(g.[value], '''') AS 字段说明, CASE WHEN COLUMNPROPERTY(a.id, a.name, ''IsIdentity'') = 1 THEN '''' ELSE '''' END AS 标识, CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = ''PK'' WHERE sc.id = a.id AND sc.colid = a.colid) THEN '''' ELSE '''' END AS 主键, b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, ''PRECISION'') AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, ''Scale''), 0) AS 小数位数,

上一篇:在括弧内可以有一或多个值,得出了从一个例子入手来学习是最快最有效 下一篇:没有了

更多阅读

韦德体育数据库字典主要包括表结构(分为

数据 2020-03-31
平时工作一会搞Oracle一会搞SqlServer,脑子都迷糊了,为了避免继续模糊,今天抽出点时间把我...
查看全文

在括弧内可以有一或多个值,得出了从一

数据 2020-03-31
在sql中要一次性删除多条记录我想到二种方法,一种是利用sqlin一种是利用循环一条条删除,下...
查看全文

--判断指定的数据库是否存在,判断临时

数据 2020-03-31
存在则删除 判断临时表是否存在 --判断指定的数据库是否存在,存在则删除 Way 1 if exists (sel...
查看全文

友情链接: 网站地图

Copyright © 2015-2019 http://www.koi-bumi.com. 韦德体育有限公司 版权所有