1.OpenRowSet例子

远程查表

 select * from openrowset('SQLOLEDB','192.168.3.81';'account';'password',Product_Db.dbo.login);
 --192.168.3.81为ip
 --account为登录账户名
 --password为密码
 --Product_Db.dbo.login为要查询的数据库表名，全称：数据库名.架构名.表名
1
2
3
4
5

远程运行sql

insert into dbo.UserInfo select * from openrowset('SQLOLEDB','10.0.1.1';'sa';'password',
'select [Created_UserId]
      ,[Created]
      ,[Description]
      ,[Code]
      ,[Name]
	  from MESDb_dev.dbo.UserInfo');
1
2
3
4
5
6
7

2.SET IDENTITY_INSERT

通常由于自增id，导致我们无法插入带id的数据，一种方法是不要自增id字段，另一种是以下的SET IDENTITY_INSERT ON方式

• 注意：这三条语句要一起执行，不能分开执行，否则会报错

SET IDENTITY_INSERT [Product_Db].[dbo].[Menu] ON --设置为允许手动插入值
INSERT INTO [Product_Db].[dbo].[login]([Id], ...) VALUES(...);--插入数据
SET IDENTITY_INSERT [Product_Db].[dbo].[Menu] OFF --//设置为禁止手动插入值
1
2
3
4

查询主外键关系

在主表DepartDment中查询主外键关系

exec sp_helpconstraint 'Department'
1

删除外键命令（注：删除前做好备份）

alter table MESDb.dbo.Department drop constraint FK_Department_Department_ParentId
alter table MESDb.dbo.Department_And_Attr drop constraint FK_Department_And_Attr_Department_DepartmentId
alter table MESDb.dbo.Line   drop constraint FK_Line_Department_DepartmentId
alter table MESDb.dbo.[User] drop constraint FK_User_Department_DepartmentId
alter table MESDb.dbo.Warehouse_And_Dept drop constraint FK_Warehouse_And_Dept_Department_DepartmentId

1
2
3
4
5
6
7

查看所有用户、添加用户

SELECT * FROM sys.server_principals;
EXEC sp_addlogin readonly,pwd
EXEC sp_adduser readonly,readonly,pwd
1
2
3

冷门而必要的SQL语句

1.SQL Server查询所有表名的名称，含Schema架构名，格式:架构名.表名

--查询所有表名的名称，含Schema架构名，格式:架构名.表名
 select concat(s.name ,concat('.',t.name)) as tableName 
 from sys.tables t left join sys.schemas s
 on t.schema_id=s.schema_id order by tableName asc;
1
2
3
4

2.查询当前数据库名称

--查询当前数据库名称
select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid)
1
2

3.SQL Server创建临时表，并把数据写入临时表

if  Object_ID('tempdb..#temp1')  is not null  
	drop table #temp1;
Select * into #temp1 from MasterData;
1
2
3

4.昨天

select getdate()-1
1

5.查询外部数据库

select  * from OPENROWSET('SQLOLEDB',
       'server=192.168.3.99;uid=sa;pwd=pwd;database=LOCAL_TEST',
       'SELECT  * FROM [dbo].[V_TEST]')
1
2
3

6.-查询数据库中所有的表名及数据量

SELECT  a.name ,  b.rows  FROM    sysobjects AS a
INNER JOIN sysindexes AS b 
ON a.id = b.id
WHERE   ( a.type = 'u' )  AND ( b.indid IN ( 0, 1 ) )
ORDER BY b.rows DESC
1
2
3
4
5

7. 外键作用

1、不执行任何操作（EF Core默认Restrict）：如果数据被其其他表外键依赖，拒绝删除
2、级联（Cascade）：删除外键表中相应的所有行
3、设置NULL（SetNull）：将表的外键字段值设置为空值NULL
4、设置默认值:如果外键表的所有外键列均已定义默认值，则将该列设置为默认值