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