2.查看环境中表的数据内容
https://<environmentURL>/?mi=SysTableBrowser&tableName=<tableName>
参数 | 涵义 | 参考值 |
mi | SysTableBrowser | mi=SysTableBrowser |
tableName | 要查看的表名称 | &TableName=SalesOrderLineV2Staging |
cmp | 法人 | &cmp=asp |
|
|
|
|
|
|
|
|
|
4.查询SQL Server中用户数据表的数据量和字段数
SELECT a.name AS '表名', b.rows as '行数', count(*) as '字段数量'
FROM sysobjects AS a INNER JOIN sysindexes
left join syscolumns
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
group by
a.name, b.rows
ORDER BY b.rows DESC
7.Azure SQL 中查询数据库死锁
WITH CTE AS (
SELECT CAST(event_data AS XML) AS [target_data_XML]
FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)
)
SELECT target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') AS Timestamp,
target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(100)') AS db_name
FROM CTE order by 1 desc
8.查询A表中是否有B字段， A= 'CCN_AssetTableStaging' ; B = 'CCNReference'
SELECT t2.name FROM SYSOBJECTS T1 INNER JOIN SYSCOLUMNS T2 ON T1.ID = T2.ID
WHERE T1.NAME = 'CCN_AssetTableStaging' AND T2.NAME = 'CCNReference'
9.查询从FO的Entity同步到Azure SQL之后staging表中每天同步的记录数（单表）
select w.pushdate,COUNT(*) counts from(
select substring(EXECUTIONID,CHARINDEX('-',EXECUTIONID)+1, 19) pushdate from CCN_GeneralJournalAccountEntryStaging) w
group by w.pushdate order by w.pushdate
10.查询从FO的Entity同步到Azure SQL之后staging表中每天同步的记录数（多表）
:OUT C:\!Work\AzureSQL-monitor\staging-datetime-0624-am.csv
SET NOCOUNT ON;--去掉每个select之后影响几行的提示
--从系统中查表名符合一定条件的表名 来循环
SELECT ROW_NUMBER() OVER (ORDER BY id ASC) rowid,id,name into #t2 from sysobjects a where a.type='u' and a.name like '%staging' order by a.name
--select * from #t2
Declare @row int, --行记录数
@count int--总记录数
Declare @tname varchar(100)--table name
select @count=COUNT(1),@row =1 from #t2
while @row <= @count --循环开始
BEGIN
select @tname=name from #t2 where rowid=@row --当前列的数据
--select @tname
declare @s nvarchar(1000) set @s = 'select '''+@tname+''' as '''+ @tname+''',w.definitiongroup, w.pushdate,COUNT(*) counts from(
select definitiongroup,substring(EXECUTIONID,CHARINDEX(''-'',EXECUTIONID)+1, 19) pushdate from '+@tname+') w
group by w.definitiongroup,w.pushdate order by w.pushdate' exec(@s) -- 成功
set @row=@row +1
END
drop table #t2
11.在Azure SQL 中有batch的执行时间字段(executionid)和每个表中数据同步的字段（syncstartdatetime）
12.在数据实体中添加了字段的属性是private ，导致可以看到但是无法赋值到staging表中，更改成public就可以了
13.在SQLServer中执行字符串表名动态循环查询内容（逗号分割字符串表名）
SELECT ROW_NUMBER() OVER (ORDER BY value ASC) rowid,value tname into #t1 FROM STRING_SPLIT('CCN_VendTransStaging,CCN_SpecTransStaging', ',')
--select * from #t1
Declare @row int, --行记录数
@count int--总记录数
Declare @tname varchar(100)--table name
select @count=COUNT(1),@row =1 from #t1
while @row <= @count --循环开始
BEGIN
select @tname=tname from #t1 where rowid=@row --当前列的数据
--select @tname
--动态slq语句：select 'CCN_SpecTransStaging' as 'CCN_SpecTransStaging',COUNT(*) counts from CCN_SpecTransStaging
declare @s nvarchar(1000) set @s = 'select '''+@tname+''' as '''+ @tname+''',COUNT(*) counts from '+@tname
print(@s)
exec(@s) -- 成功
set @row=@row +1
END
drop table #t1
