SQLSERVER 的一些常用知识总结

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

1.SQLserver忘记密码修改方法

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

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

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

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

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

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

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

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

4.查看库中全部的表

CREATE VIEW dbo.ALL_TABLES
AS
SELECT 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 小数位数,
      CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
      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
GO

5 查看库中全部的索引

CREATE VIEW dbo.ALL_INDEXS
AS
SELECT TOP 100 PERCENT a.ID,
      CASE WHEN b.keyno = 1 THEN c.name ELSE '' END AS TableName,
      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
GO

6.SQLSERVER中多表关联update的写法
update a set a.name=b.name from tb1 a,tb2 b where a.bid = b.id
与ORACLE中实现的对比
update tb1 a set a.name= (select name from tb2 b where a.id=b.id )

7.用查询分析器连接只开放了1433端口的数据库服务
SQL SERVER 客户端网络实用工具，别名选项卡－> 添加－> 服务器别名和服务器名称填写目标数据库的IP即可，网络库选择TCP/IP.

8.把某个字段重新生成序列(从1到n)
DECLARE @i int
Set @i = 0
Update TableName Set @i = @i + 1, FieldName = @i

9.常用函数使用示例
    select cast(getdate() as varchar(20))--强制转换为字符串
    select cast('111.11'as numeric(15,2))--强制转换为数字
    select len(ltrim(rtrim(' syj ')))--取字符串长度
    select substring('abcdefg',2,3)--截取字符串
    select isNull(null,'空')--判断为空
    select dateadd(dd, 1,getdate())--加一天
    select dateadd(yy, 1,getdate())--加一年
    select dateadd(mm, -1,getdate())--加一月
    select datepart(yy,getdate())--取年
    select datepart(mm,getdate())--取月
    select datepart(dd,getdate())--取日
    select convert(varchar(10),getdate(),120)--格式化yyyy-mm-dd
    select convert(varchar(20),getdate(),120)--格式化yyyy-mm-dd HH:MM:ss
    select datediff(dd,'2000-01-01',getdate())--天间隔
    select datediff(mm,'2000-01-01',getdate())--月间隔
    select datediff(yy,'2000-01-01',getdate())--年间隔
    case的使用
    select
 tb.f1,case tb.f1 when 'a' then 'is a' when 'b' then 'is b' else 'is err' end
    from
 (select 'a' as f1
 union all
 select 'b' as f1
 union all
        select 'c' as f1) tb
    把一个表翻3倍
    select tb2.* from tb2, (select '1' as f1 union select '2' as f1 union select '3' as f1) tb