开发知识总结

1.跨库查询

EXEC sp_addlinkedserver '172.16.100.201',N'SQL Server'
EXEC sp_addlinkedsrvlogin '172.16.100.201', 'false', NULL, 'sa', 'mima'

SELECT * FROM dbo.SYS_Log WHERE SPBillID IN (
SELECT SPBillID FROM [172.16.100.201].TMS_BI_Prod.dbo.BI_SOHeader WHERE ApproveDate IS NULL AND ApprovedDate IS NOT NULL
)
exec sp_dropserver '172.16.100.201', 'droplogins'

2.修改数据库字段名称

EXEC sp_rename 'BI_Inventory.SQuantity','SAPQuantity','COLUMN'

3.查询数据库连接数

select COUNT(*) from sysprocesses where dbid= db_id('TMS_BI_PROD')

3.创建聚集索引 

alter table SYS_EventPool drop constraint  PK__SYS_Even__8A871BEE216E4E9F 

CREATE clustered index ix_CreatedTime on  dbo.SYS_EventPool( CreatedTime)