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的时候因为字段过长导致报错。