• SQLServer


    @、将检索到的数据插入到一张新表

    SELECT * INTO <NEW_TABLE_NAME> FROM <OLD_TABLE_NAME>

    @、sqlserver2000查询表结构

    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,'IsIdentity')=1 then '√'else '' end,   
    主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (  
    SELECT name FROM sysindexes WHERE indid in(   
    SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid   
    ))) then '√' else '' end,   
    类型=b.name,   
    占用字节数=a.length,   
    长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),   
    小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),   
    答应空=case when a.isnullable=1 then '√'else '' end,   
    默认值=isnull(e.text,''),   
    字段说明=isnull(g.[value],'')   
    FROM syscolumns a   
    left join systypes b on a.xusertype=b.xusertype   
    inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'   
    left join syscomments e on a.cdefault=e.id   
    left join sysproperties g on a.id=g.id and a.colid=g.smallid   
    left join sysproperties f on d.id=f.id and f.smallid=0   
    where d.name='TABLE_NAME' --假如只查询指定表,加上此条件   
    order by a.id,a.colorder 

    @、sqlserver2008查询表结构

    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,
         字段说明   = isnull(G.[value],''),
         标识       = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,
         主键       = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (
                          SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,
         类型       = B.name,
         占用字节数 = A.Length,
         长度       = COLUMNPROPERTY(A.id,A.name,'PRECISION'),
         小数位数   = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),
         允许空     = Case When A.isnullable=1 Then '√'Else '' End,
         默认值     = isnull(E.Text,'')
     FROM
         syscolumns A
     Left Join
         systypes B
     On
         A.xusertype=B.xusertype
     Inner Join
         sysobjects D
     On
         A.id=D.id  and D.xtype='U' and  D.name<>'dtproperties'
     Left Join
         syscomments E
     on
         A.cdefault=E.id
     Left Join
     sys.extended_properties  G
     on
         A.id=G.major_id and A.colid=G.minor_id
     Left Join

     sys.extended_properties F
     On
         D.id=F.major_id and F.minor_id=0
         --where d.name='OrderInfo'    --如果只查询指定表,加上此条件
     Order By
         A.id,A.colorder

    @、数据操作

    -- 获取连到数据库的进程的相关信息 NET_ADDRESS,HOSTNAME
    SELECT * FROM MASTER.DBO.SYSPROCESSES  WHERE SPID=@@SPID
    -- 获取数据库服务器时间
    SELECT GETDATE()
    -- 日期转换
    CONVERT(DATETIME,'2016-11-18 00:00:00.000', 120)
    CONVERT(DATE,'11/30/2016', 110)
    /*
    108 hh:mm:ss
    111 yy/mm/dd
    112 yymmdd
    120或者20 yyyy-mm-dd hh:mi:ss(24h)
    121或者21 yyyy-mm-dd hh:mi:ss.mmm(24h)
    */
    --字符串拼接后转datetime型
    CAST(CONVERT(VARCHAR(10),@ldt_settle_bdate,120) + ' 00:00:00'  AS DATETIME)

    -- 获取表的主键信息
    SELECT * FROM SYSOBJECTS WHERE PARENT_OBJ=OBJECT_ID('T_MAL_NEGOTIATE_ATTACH') AND XTYPE='PK'
    -- 获取表信息(COUNT(*)来判断表是否存在)
    SELECT * FROM SYSOBJECTS WHERE ID=OBJECT_ID('T_MAL_NEGOTIATE_ATTACH') AND XTYPE='U'
    -- 获取表对应的列信息(加上NAME来判断列是否存在,通过LENGTH来获取列的长度)
    SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID('T_MAL_NEGOTIATE_ATTACH')
    -- 获取表的索引
    SELECT * FROM SYSINDEXES WHERE ID=OBJECT_ID('T_MAL_NEGOTIATE_ATTACH')
    -- 获取表的索引列
    SELECT * FROM SYSINDEXKEYS WHERE ID=OBJECT_ID('T_MAL_NEGOTIATE_ATTACH')
    -- 获取表主键对应的列
    SELECT O.NAME AS 表名,C.NAME AS 字段名,K.COLID AS 字段序号,K.KEYNO AS 索引顺序
                FROM SYSINDEXES I
                JOIN SYSINDEXKEYS K ON I.ID = K.ID AND I.INDID = K.INDID
                JOIN SYSOBJECTS O ON I.ID = O.ID
                JOIN SYSCOLUMNS C ON I.ID=C.ID AND K.COLID = C.COLID
                WHERE O.XTYPE = 'U'
                AND EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE = 'PK' AND NAME =
                I.NAME) AND O.NAME='TABLE_NAME'--表名称
                ORDER BY O.NAME,K.COLID

    --判断某张表的主键是否包含某一字段

    SELECT COUNT(*) INTO :ll_cnt
    FROM SYSINDEXES I
    JOIN SYSINDEXKEYS K ON I.ID = K.ID AND I.INDID = K.INDID
    JOIN SYSOBJECTS O ON I.ID = O.ID
    JOIN SYSCOLUMNS C ON I.ID=C.ID AND K.COLID = C.COLID
    WHERE O.XTYPE = 'U' AND O.NAME='TABLE_NAME(表名)' AND C.NAME = 'COLUMN_NAME(列名)'
        AND EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE = 'PK' AND NAME = I.NAME) ;
                
            
    SELECT REQUEST_SESSION_ID SPID, OBJECT_NAME(RESOURCE_ASSOCIATED_ENTITY_ID) TABLENAME   
    FROM SYS.DM_TRAN_LOCKS WHERE RESOURCE_TYPE='OBJECT'     
    --SPID   锁表进程
    --TABLENAME   被锁表名
     
    -- 解锁    
    DECLARE @SPID  INT
    SET @SPID  = 62 --锁表进程
    DECLARE @SQL VARCHAR(1000)
    SET @SQL='KILL ' + CAST(@SPID  AS VARCHAR)
    EXEC(@SQL)    
            
    -- 创建DBLink
    exec sp_addlinkedserver 'dblinked_name','','SQLOLEDB','192.168.1.107,1433'
    exec sp_addlinkedsrvlogin 'dblinked_name','false',null,'sa','123'
    其中 dblinked_name 为DBLink的名字
    使用DBLink
    DBLink名字.数据库名.dbo.数据库对象名
    如:dblinked_name.db_name.dbo.table_name

    --把多行同列的数据合并

    select
    类别,
        名称 = (
            stuff(
                (select ',' + 名称 from Table_A where 类别 = A.类别 for xml path('')),
                1,
                1,
                ''
            )
        )
    from Table_A as A group by 类别

    原文:https://zhidao.baidu.com/question/1047303001913272259.html

    https://blog.csdn.net/u012860938/article/details/50803429

    @、Sqlserver2000在过程里直接通过sql语句drop table xxx 的动作,可是xxx表就是没有被删除,这种情况需要动态执行,比如:

    select @ls_sql = 'if exists(select 1 from sysobjects where id = object_id(''table_name'') and  type = ''U'') drop table table_name'  
    exec(@ls_sql)

    @、Sqlserver2000安装完之后,1433端口还是不能被访问

    需要安装sp4补丁包,并重启服务。

    @、Sqlserver2008查找含有某一个字段的表

    SELECT PBC_TNAM,PBC_CNAM FROM DBO.PBCATCOL WHERE PBC_CNAM = :colum_name ORDER BY PBC_TNAM ;

    @、Substring汉字的处理

    原文:http://www.makaidong.com/SQL/317296.shtml

    SELECT SUBSTRING(CONVERT(TEXT, 'abc含有汉字的字符串abc'), 1, 10),这样在SUBSTIRNG得到的就不是按字符数截取的数据,从而避免在INSERT的时候因为字段过长导致报错。

  • 相关阅读:
    Goland在go mod vendor模式下无法识别某些库
    国际教育游戏实证研究综述:2008年-2012年
    ES6之用let,const和用var来声明变量的区别
    集成spring boot + mysql + docker实战
    js sort方法根据数组中对象的某一个属性值进行排序(实用方法)
    小试牛刀之sort()排序的实现
    Redis字符串(STRING)中BIT相关命令
    koa,express,node 通用方法连接MySQL
    JavaScript预解释是一种毫无节操的机制
    [php]如何做到高并发优化
  • 原文地址:https://www.cnblogs.com/yarightok/p/6127045.html
Copyright © 2020-2023  润新知