C#中如何获取表的结构

sql数据库的话,建议你限看下本库的系统表(需要编辑Sql注册属性的查看系统表)

可以通过查询 syscolumns,sysobjects,sysindexkeys 获得你想要的东西

Accsee的话

查询MSysNavPaneGroups系统隐藏表字段(Flags|GroupCategoryID | Id | Name | Object Type Group | ObjectID |Position)

Name是用户表名称,[Object Type Group]的值为 "-1"

查询MSysNavPaneObjectIDs系统隐藏表字段(ID,Name,Type)

MSysQueries,视图相关信息

1,Expression,视图表达式(部分Sql语句)

2,Name1,Name2,分别为视图名和视图字段

ubuntu下使用snmp，我们可以使用mibs库的OID来查询我们想要得到的结果，例如：

snmpwalk -v 2c -c public localhost 1361212522

但是这个OID的号码很不容易让我们记住，还是字符串更让人容易记忆，今天学习python的时候，借用老师的一个方法，终于实现了snmp支持mibs库的字符串形式查询，好记性不如烂笔头啊，记录一下吧，年纪大了总是记不住

方法如下：

首先更改snmpconf文件

vi /etc/snmp/snmpconf

将最后一行的mibs注释，然后再shell中执行如下命令

apt-get install snmp-mibs-downloader

完成该步骤继续执行如下命令

download-mibs

ok！到这里我们可以使用字符串来查询信息了。

snmpwalk -v 2c -c public localhost system

SNMPv2-MIB::sysDescr0 = STRING: Linux addam-virtual-machine 380-29-generic #42~precise1-Ubuntu SMP Wed Aug 14 15:31:16 UTC 2013 i686

SNMPv2-MIB::sysObjectID0 = OID: NET-SNMP-MIB::netSnmpAgentOIDs10

DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (189455) 0:31:3455

SNMPv2-MIB::sysContact0 = STRING: Me <me@exampleorg>

给你一些我常用的脚本！至于方案，我觉得完全在于积累经验！

--前10名其他等待类型

SELECT TOP 10

from sysdm_os_wait_stats

ORDER BY wait_time_ms DESC

SELECT FROM sysdm_os_wait_stats WHERE wait_type like 'PAGELATCH%'

OR wait_type like 'LAZYWRITER_SLEEP%'

--CPU的压力

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sysdm_os_schedulers

WHERE scheduler_id < 255

--表现最差的前10名使用查询

SELECT TOP 10 ProcedureName = ttext,

ExecutionCount = sexecution_count,

AvgExecutionTime = isnull ( stotal_elapsed_time / sexecution_count, 0 ),

AvgWorkerTime = stotal_worker_time / sexecution_count,

TotalWorkerTime = stotal_worker_time,

MaxLogicalReads = smax_logical_reads,

MaxPhysicalReads = smax_physical_reads,

MaxLogicalWrites = smax_logical_writes,

CreationDateTime = screation_time,

CallsPerSecond = isnull ( sexecution_count / datediff ( second , screation_time, getdate ()), 0 )

FROM sysdm_exec_query_stats s

CROSS APPLY sysdm_exec_sql_text( ssql_handle ) t ORDER BY

smax_physical_reads DESC

SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms总信号等待时间 ,

SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms资源的等待时间,

SUM(signal_wait_time_ms) 10 / SUM (wait_time_ms) 100 AS [signal_wait_percent信号等待％],

SUM(wait_time_ms - signal_wait_time_ms) 10 / SUM (wait_time_ms) 100 AS [resource_wait_percent资源等待％]

FROM sysdm_os_wait_stats

--一个信号等待时间过多对资源的等待时间那么你的CPU是目前的一个瓶颈。

--查看进程所执行的SQL语句

if (select COUNT() from masterdbosysprocesses) > 500

begin

select text,CROSS APPLY mastersysdm_exec_sql_text(asql_handle) from mastersyssysprocesses a

end

select text,a from mastersyssysprocesses a

CROSS APPLY mastersysdm_exec_sql_text(asql_handle)

where aspid = '51'

dbcc inputbuffer(53)

with tb

as

(

select blocking_session_id,

session_id,db_name(database_id) as dbname,text from mastersysdm_exec_requests a

CROSS APPLY mastersysdm_exec_sql_text(asql_handle)

),

tb1 as

(

select a,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage8 as 'memory_usage(KB)',

total_scheduled_time,reads,writes,logical_reads

from tb a inner join mastersysdm_exec_sessions b

on asession_id=bsession_id

)

select a,connect_time,client_tcp_port,client_net_address from tb1 a inner join mastersysdm_exec_connections b on asession_id=bsession_id

--当前进程数

select from masterdbosysprocesses

order by cpu desc

--查看当前活动的进程数

sp_who active

--查询是否由于连接没有释放引起CPU过高

select from masterdbosysprocesses

where spid> 50

and waittype = 0x0000

and waittime = 0

and status = 'sleeping '

and last_batch < dateadd(minute, -10, getdate())

and login_time < dateadd(minute, -10, getdate())

--强行释放空连接

select 'kill ' + rtrim(spid) from masterdbosysprocesses

where spid> 50

and waittype = 0x0000

and waittime = 0

and status = 'sleeping '

and last_batch < dateadd(minute, -60, getdate())

and login_time < dateadd(minute, -60, getdate())

--查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)

select spid,cmd,cpu,physical_io,memusage,

(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text

from mastersysprocesses order by cpu desc,physical_io desc

--查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局

SELECT TOP 100 usecounts, objtype, psize_in_bytes,[sql][text]

FROM sysdm_exec_cached_plans p OUTER APPLY sysdm_exec_sql_text (pplan_handle) sql

ORDER BY usecounts,psize_in_bytes desc

SELECT top 25 qttext,qsplan_generation_num,qsexecution_count,dbid,objectid

FROM sysdm_exec_query_stats qs

CROSS APPLY sysdm_exec_sql_text(sql_handle) as qt

WHERE plan_generation_num >1

ORDER BY qsplan_generation_num

SELECT top 50 qttext AS SQL_text ,SUM(qstotal_worker_time) AS total_cpu_time,

SUM(qsexecution_count) AS total_execution_count,

SUM(qstotal_worker_time)/SUM(qsexecution_count) AS avg_cpu_time,

COUNT() AS number_of_statements

FROM sysdm_exec_query_stats qs

CROSS APPLY sysdm_exec_sql_text(qssql_handle) as qt

GROUP BY qttext

ORDER BY total_cpu_time DESC --统计总的CPU时间

--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间

-- 计算可运行状态下的工作进程数量

SELECT COUNT() as workers_waiting_for_cpu,sscheduler_id

FROM sysdm_os_workers AS o

INNER JOIN sysdm_os_schedulers AS s

ON oscheduler_address=sscheduler_address

AND sscheduler_id<255

WHERE ostate='RUNNABLE'

GROUP BY sscheduler_id

--表空间大小查询

create table #tb(表名 sysname,记录数 int,保留空间 varchar(100),使用空间 varchar(100),索引使用空间 varchar(100),未用空间 varchar(100))

insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused '''''

select from #tb

go

SELECT

表名,

记录数,

cast(ltrim(rtrim(replace(保留空间,'KB',''))) as int)/1024 保留空间MB,

cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 使用空间MB,

cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024/102400 使用空间GB,

cast(ltrim(rtrim(replace(索引使用空间,'KB',''))) as int)/1024 索引使用空间MB,

cast(ltrim(rtrim(replace(未用空间,'KB',''))) as int)/1024 未用空间MB

FROM #tb

WHERE cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 > 0

--order by 记录数 desc

ORDER BY 使用空间MB DESC

DROP TABLE #tb

--查询是否由于连接没有释放引起CPU过高

select from masterdbosysprocesses

where spid> 50

and waittype = 0x0000

and waittime = 0

and status = 'sleeping '

and last_batch < dateadd(minute, -10, getdate())

and login_time < dateadd(minute, -10, getdate())

--强行释放空连接

select 'kill ' + rtrim(spid) from masterdbosysprocesses

where spid> 50

and waittype = 0x0000

and waittime = 0

and status = 'sleeping '

and last_batch < dateadd(minute, -60, getdate())

and login_time < dateadd(minute, -60, getdate())

----查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)

select spid,cmd,cpu,physical_io,memusage,

(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text

from mastersysprocesses order by cpu desc,physical_io desc

----查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局

SELECT TOP 100 usecounts, objtype, psize_in_bytes,[sql][text]

FROM sysdm_exec_cached_plans p OUTER APPLY sysdm_exec_sql_text (pplan_handle) sql

ORDER BY usecounts,psize_in_bytes desc

SELECT top 25 qttext,qsplan_generation_num,qsexecution_count,dbid,objectid

FROM sysdm_exec_query_stats qs

CROSS APPLY sysdm_exec_sql_text(sql_handle) as qt

WHERE plan_generation_num >1

ORDER BY qsplan_generation_num

SELECT top 50 qttext AS SQL_text ,SUM(qstotal_worker_time) AS total_cpu_time,

SUM(qsexecution_count) AS total_execution_count,

SUM(qstotal_worker_time)/SUM(qsexecution_count) AS avg_cpu_time,

COUNT() AS number_of_statements

FROM sysdm_exec_query_stats qs

CROSS APPLY sysdm_exec_sql_text(qssql_handle) as qt

GROUP BY qttext

ORDER BY total_cpu_time DESC --统计总的CPU时间

--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间

-- 计算可运行状态下的工作进程数量

SELECT COUNT() as workers_waiting_for_cpu,sscheduler_id

FROM sysdm_os_workers AS o

INNER JOIN sysdm_os_schedulers AS s

ON oscheduler_address=sscheduler_address

AND sscheduler_id<255

WHERE ostate='RUNNABLE'

GROUP BY sscheduler_id

SELECT creation_time N'语句编译时间'

,last_execution_time N'上次执行时间'

,total_physical_reads N'物理读取总次数'

,total_logical_reads/execution_count N'每次逻辑读次数'

,total_logical_reads N'逻辑读取总次数'

,total_logical_writes N'逻辑写入总次数'

, execution_count N'执行次数'

, total_worker_time/1000 N'所用的CPU总时间ms'

, total_elapsed_time/1000 N'总花费时间ms'

, (total_elapsed_time / execution_count)/1000 N'平均时间ms'

,SUBSTRING(sttext, (qsstatement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(sttext)

ELSE qsstatement_end_offset END

- qsstatement_start_offset)/2) + 1) N'执行语句'

FROM sysdm_exec_query_stats AS qs

CROSS APPLY sysdm_exec_sql_text(qssql_handle) st

where SUBSTRING(sttext, (qsstatement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(sttext)

ELSE qsstatement_end_offset END

- qsstatement_start_offset)/2) + 1) not like '%fetch%'

ORDER BY total_elapsed_time / execution_count DESC;

主键约束

SELECT

tabname AS [表名],

idxname AS [主键名称],

colname AS [主键列名]

FROM

sysindexes idx

JOIN sysindex_columns idxCol

ON (idxobject_id = idxColobject_id

AND idxindex_id = idxColindex_id

AND idxis_primary_key = 1)

JOIN systables tab

ON (idxobject_id = tabobject_id)

JOIN syscolumns col

ON (idxobject_id = colobject_id

AND idxColcolumn_id = colcolumn_id);

唯一约束

SELECT

tabname AS [表名],

idxname AS [约束名称],

colname AS [约束列名]

FROM

sysindexes idx

JOIN sysindex_columns idxCol

ON (idxobject_id = idxColobject_id

AND idxindex_id = idxColindex_id

AND idxis_unique_constraint = 1)

JOIN systables tab

ON (idxobject_id = tabobject_id)

JOIN syscolumns col

ON (idxobject_id = colobject_id

AND idxColcolumn_id = colcolumn_id);

根据报错信息，ORA-00001: 违反唯一约束条件，一般是指存在了唯一性冲突，数据不唯一导致数据库拒绝 *** 作。以下从原因和解决方式提供下思路，供参考：

原因：表中从在唯一索引，在插入或更新被索引字段数据时插入或更新的数据已经从在于被索引的字段。

解决办法：

1、修改要插入或更新的数据，使其不予被索引的数据重复。

2、检查索引是否合适，如果不是业务需要删除唯一索引。

以上就是关于C#中如何获取表的结构全部的内容，包括:C#中如何获取表的结构、如何在ubuntu下使snmp支持mibs库字符串形式、谁总结下sqlserver数据库优化知识的详解和具体 *** 作最优方案！等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！