@、将检索到的数据插入到一张新表
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的时候因为字段过长导致报错。