查询表属性
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
克隆表
如果要插入目标表已经存在：
insert into 目的表 select * from 表 where 条件
如果是跨数据库操作的话： 怎么把A数据库的atable表所查询的东西，全部插入到B 数据库的btable表中
select * into B.btable from A.atable where ...
同样，如果是跨服务器的，也是可以的。
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。