sql 备忘录

查询表属性

SELECT a.name [column], b.name type, a.prec length, isnull(c.value, ' ') [description],CASE a.isnullable  WHEN 0 THEN '否' WHEN 1 THEN '是' END AS '是否为空'
,d.COLUMN_DEFAULT AS 默认值 FROM syscolumns a
LEFT OUTER JOIN systypes b ON a.xusertype = b.xusertype
LEFT OUTER JOIN sys.extended_properties c ON a.id = c.major_id AND a.colid = c.minor_id 
LEFT JOIN INFORMATION_SCHEMA.COLUMNS d ON a.name= d.COLUMN_NAME AND a.id=object_id(d.TABLE_NAME)
WHERE a.id = object_id('表')
ORDER BY a.colorder;

 模糊查询表

SELECT * FROM sys.tables WHERE name LIKE '%表%'

查询有重复数据

SELECT *,COUNT(*) repeatNum FROM (SELECT DepCode,ArrCode,DepTime FROM dbo.table1 WHERE BuyTime>='2020-4-29' AND BuyTime<'2020-4-30' AND OTAShopId=13 AND AirCode='CA') R
 group by DepCode,ArrCode,DepTime having count(*) > 1

克隆表

SELECT * INTO 表2 FROM 表

如果要插入目标表已经存在：

insert into 目的表 select * from 表 where 条件

如果是跨数据库操作的话： 怎么把A数据库的atable表所查询的东西，全部插入到B 数据库的btable表中

select * into B.btable from A.atable where ...

同样，如果是跨服务器的，也是可以的。