sqlserver獲取
如何獲取SQL Server資料庫元數據
元數據簡介
元數據 (metadata) 最常見的定義為「有關數據的結構數據」，或再簡單一點就是「關於數據的信息」，日常生活中的圖例、圖書館目錄卡和名片等都能夠看作是元數據。在關系型資料庫管理系統 (DBMS) 中，元數據描述了數據的結構和意義。比如在管理、維護 SQL Server 或是研發資料庫應用程式的時候，我們經常要獲取一些涉及到資料庫架構的信息：
某個資料庫中的表和視圖的個數連同名稱;
某個表或視圖中列的個數連同每一列的名稱、數據類型、長度、精度、描述等;
某個表上定義的約束;
某個表上定義的索引連同主鍵/外鍵的信息。
下面我們將介紹幾種獲取元數據的方法。
獲取元數據
使用系統存儲過程和系統函數訪問元數據
獲取元數據最常用的方法是使用 SQL Server 提供的系統存儲過程和系統函數。
系統存儲過程和系統函數在系統表和元數據之間提供了一個抽象層，使得我們不用直接查詢系統表就能獲得當前資料庫對象的元數據。
常用的和元數據有關的系統存儲過程有以下一些：
系統存儲過程
sp_columns 返回指定表或視圖的列的周詳信息。
sp_databases 返回當前伺服器上的任何資料庫的基本信息。
sp_fkeys 若參數為帶有主鍵的表，則返回包含指向該表的外鍵的任何錶;若參數為帶有外鍵的表名，則返回任何同過主鍵/外鍵關系和該外鍵相關聯的任何錶。
sp_pkeys 返回指定表的主鍵信息。
sp_server_info 返回當前伺服器的各種特性及其對應取值。
sp_sproc_columns 返回指定存儲過程的的輸入、輸出參數的信息。
sp_statistics 返回指定的表或索引視圖上的任何索引連同統計的信息。
sp_stored_proceres 返回當前資料庫的存儲過程列表，包含系統存儲過程。
sp_tables 返回當前資料庫的任何錶和視圖，包含系統表。
常用的和元數據有關的系統函數有以下一些：
系統函數
COLUMNPROPERTY 返回有關列或過程參數的信息，如是否允許空值，是否為計算列等。
COL_LENGTH 返回指定資料庫的指定屬性值，如是否處於只讀模式等。
DATABASEPROPERTYEX 返回指定資料庫的指定選項或屬性的當前配置，如資料庫的狀態、恢復模型等。
OBJECT_ID 返回指定資料庫對象名的標識號
OBJECT_NAME 返回指定資料庫對象標識號的對象名。
OBJECTPROPERTY 返回指定資料庫對象標識號的有關信息，如是否為表，是否為約束等。
fn_listextendedproperty 返回資料庫對象的擴展屬性值，如對象描述、格式規則、輸入掩碼等。
由於我們無法直接利用到存儲過程和函數的返回結果，因此只有在我們關心的只是查詢的結果，而無需進一步利用這些結果的時候，我們會使用系統存儲過程和系統函數來查詢元數據。
例如，假如要獲得當前伺服器上任何資料庫的基本信息，我們能夠在查詢分析器裡面運行：
EXEC sp_databases
GO
在返回結果中我們能夠看到資料庫的名稱、大小及備注等信息。
但是假如要引用這部分信息，或存儲這部分信息以供後面使用，那麼我們必須藉助中間表來完成這個操作：
CREATE TABLE #sp_result
(
DATABASE_NAME sysname,
DATABASE_SIZE int,
REMARKS varchar(254) NULL
)
GO
INSERT INTO #sp_result
EXEC ('sp_databases')
GO
使用信息架構視圖訪問元數據
信息架構視圖基於 SQL-92 標准中針對架構視圖的定義，這些視圖單獨於系統表，提供了關於 SQL Server 元數據的內部視圖。信息架構視圖的最大長處是，即使我們對系統表進行了重要的修改，應用程式也能夠正常地使用這些視圖進行訪問。因此對於應用程式來說，只要是符合 SQL-92 標準的資料庫系統，使用信息架構視圖總是能夠正常工作的。
信息架構視圖
INFORMATION_SCHEMA.CHECK_CONSTRAINTS：返回有關列或過程參數的信息，如是否允許空值，是否為計算列等。
INFORMATION_SCHEMA.COLUMNS：返回當前資料庫中當前用戶能夠訪問的任何列及其基本信息。
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE：返回當前資料庫中定義了約束的任何列及其約束名。
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE：返回當前資料庫中定義了約束的任何錶及其約束名。
INFORMATION_SCHEMA.KEY_COLUMN_USAGE：返回當前資料庫中作為主鍵/外鍵約束的任何列。
INFORMATION_SCHEMA.SCHEMATA：返回當前用戶具備許可權的任何資料庫及其基本信息。
INFORMATION_SCHEMA.TABLES：返回當前用戶具備許可權的當前資料庫中的任何錶或視圖及其基本信息。
INFORMATION_SCHEMA.VIEWS：返回當前資料庫中的當前用戶能夠訪問的視圖及其任何者、定義等信息。
由於這些信息架構都是以視圖的方式存在的，因此我們能夠很方便地獲得並利用需要的信息。
例如，我們要得到某個表有多少列，能夠使用以下語句：
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='mytable'
使用系統表訪問元數據
雖然使用系統存儲過程、系統函數和信息架構視圖已能夠為我們提供了相當豐富的元數據信息，但是對於某些特別的元數據信息，我們仍然需要直接對系統表進行查詢。因為SQL Server 將任何資料庫對象的信息均存放在系統表中，作為 SQL Server 的管理、研發人員，了解各個系統表的作用將有助於我們了解 SQL Server 的內在工作原理。
SQL Server 的系統表很多，其中最常用的和元數據查詢有關的表有如下一些：
syscolumns 存儲每個表和視圖中的每一列的信息連同存儲過程中的每個參數的信息。
syscomments 存儲包含每個視圖、規則、默認值、觸發器、CHECK 約束、DEFAULT 約束和存儲過程的原始 SQL 文本語句。
sysconstraints 存儲當前資料庫中每一個約束的基本信息。
sysdatabases 存儲當前伺服器上每一個資料庫的基本信息。
sysindexes 存儲當前資料庫中的每個索引的信息。
sysobjects 存儲資料庫內的每個對象(約束、默認值、日誌、規則、存儲過程等)的基本信息。
sysreferences 存儲任何包括 FOREIGN KEY 約束的列。
systypes 存儲系統提供的每種數據類型和用戶定義數據類型的周詳信息。
將系統存儲過程、系統函數、信息架構視圖和系統表結合使用，能夠方便地讓我們獲得任何需要的元數據信息。
示例：
1、 獲得當前資料庫任何用戶表的名稱。
SELECT OBJECT_NAME (id)
FROM sysobjects
WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
其中主要用到了系統表 sysobjects連同其屬性 xtype，更有就是用到了 OBJECTPROPERTY 系統函數來判斷是不是安裝 SQL Server 的過程中創建的對象。
2、獲得指定表上任何的索引名稱。
SELECT name FROM sysindexes
WHERE id = OBJECT_ID ('mytable') AND indid > 0
綜合實例
下面給出了一個存儲過程，他的作用是自動將當前資料庫的用戶存儲過程加密。
DECLARE @sp_name nvarchar(400)
DECLARE @sp_content nvarchar(2000)
DECLARE @asbegin int
declare @now datetime
select @now = getdate()
DECLARE sp_cursor CURSOR FOR
SELECT object_name(id)
FROM sysobjects
WHERE xtype = 'P'
AND type = 'P'
AND crdate < @now
AND OBJECTPROPERTY(id, 'IsMSShipped')=0
OPEN sp_cursor
FETCH NEXT FROM sp_cursor
INTO @sp_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp_content = text FROM
syscomments WHERE id = OBJECT_ID(@sp_name)
SELECT @asbegin =
PATINDEX ( '%AS' + char(13) + '%', @sp_content)
SELECT @sp_content =
SUBSTRING(@sp_content, 1, @asbegin - 1)
+ ' WITH ENCRYPTION AS'
+ SUBSTRING (@sp_content, @asbegin+2, LEN(@sp_content))
SELECT @sp_name = 'DROP PROCEDURE [' + @sp_name + ']'
EXEC sp_executesql @sp_name
EXEC sp_executesql @sp_content
FETCH NEXT FROM sp_cursor
INTO @sp_name
END
CLOSE sp_cursor
DEALLOCATE sp_cursor
該存儲過程利用了 sysobjects 和 syscomments 表，並巧妙地修改了原存儲過程的 SQL 定義語句，將 AS 修改為了 WITH ENCRYPTION AS，從而達到了加密存儲過程的目的。本存儲過程在 SQL Server 2000 上通過。
㈡ 如何在sqlserver中獲取表的所有列信息
1.查詢資料庫中的所有資料庫名：
SELECT Name FROM Master..SysDatabases ORDER BY Name
2.查詢某個資料庫中所有的表名：
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
3.查詢表結構信息：
SELECT (case when a.colorder=1 then d.name else null end) 表名,
a.colorder 欄位序號,a.name 欄位名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 標識,
(case when (SELECT count(*) FROM sysobjects
WHERE (name in (SELECT name FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))
AND (xtype = 'PK'))>0 then '√' else '' end) 主鍵,b.name 類型,a.length 佔用位元組數,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 長度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小數位數,(case when a.isnullable=1 then '√'else '' end) 允許空,
isnull(e.text,'') 默認值,isnull(g.[value], ' ') AS [說明]
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.class and f.minor_id=0
where b.name is not null
--WHERE d.name='要查詢的表' --如果只查詢指定表,加上此條件
order by a.id,a.colorder
㈢ sql server 怎麼獲取表中的數據
（1）啟動Microsoft SQL Server Management Studio程序 （2）按提示操作進入系統 （3）選擇那個建好表的資料庫 （4）點擊滑鼠右鍵，會彈出一個菜單，選擇菜單命令：任務/導出數據
㈣ 如何獲取SQL server資料庫的連接字元串
步驟：
1、新建一個txt文本文檔，然後把後綴名改為udl
2、打開 新建文本文檔.udl ，並選擇相應的資料庫進行連接，之後點擊 「測試連接」，成功之後關閉。
3、將後綴名改回txt，打開txt文本文檔就可以看到已經創建好了資料庫連接字元串了。
㈤ sqlserver中怎樣獲取名稱許可權
使用SQL語句。
使用SQL語句獲取SQLServer資料庫登錄用戶許可權。
資料庫用戶是資料庫級別的主體,被用於訪問資料庫層面的對象。每一個資料庫用戶都必須要一個與之對用的登錄名。資料庫用戶的信息存在資料庫中，而登錄名存在實例級別的Master資料庫中(但SQL SERVER2012的Contained Database允許將登錄名也存在資料庫級別)。通常來說，資料庫層級的用戶可以和映射的登錄名不一致，但由於這種做法會引起混淆，因此並不推薦。
㈥ 如何在SqlServer中獲取前端連接的IP地址，計算機名等信息
如果你對SqlServer的系統函數或視圖不太了解，這個功能看起來好像比較復雜，而實際上， SqlServer的動態管理視圖已經給我們提供了這些信息，下面我們來看兩個動態管理視圖。1、Sys.dm_exec_Sessions 這個視圖中提供了所有連接sqlserver的客戶端的一些信息，下面是Sys.dm_exec_Sessions返回的列：列名數據類型 說明Session_idsmallint標識與每個活動主連接關聯的會話。login_timedatetime建立會話的時間。host_namenvarchar(128)與會話關聯的主機。program_namenvarchar(128)與會話關聯的程序。host_process_idint與會話關聯的進程 ID。client_versionint客戶端連接到伺服器所用的介面版本。client_interface_namenvarchar(32)客戶端連接到伺服器所用的介面名稱。security_idvarbinary(85)與登錄名關聯的 Microsoft Windows 安全 ID。login_namenvarchar(128)與會話關聯的 SQL 登錄名。nt_domainnvarchar(128)從中建立會話連接的域。nt_user_namenvarchar(128)與會話關聯的用戶名。statusnvarchar(30)會話的狀態。可能的值： 1,運行 - 當前正在運行一個或多個請求 2,睡眠 - 當前沒有運行任何請求 3,休眠 - 會話處於登錄前狀態 context_infovarbinary(128)會話的 CONTEXT_INFO 值。cpu_timeint該會話所佔用的 CPU 時間（毫秒）。memory_usageint該會話所佔用的 8 KB 內存頁數。total_scheled_timeint計劃內含請求的會話的執行所耗用的總計時間（毫秒）。total_elapsed_timeint自會話建立以來已耗用的時間（毫秒）。endpoint_idint與會話關聯的端點的 ID。last_request_start_timedatetime最近一次會話請求的開始時間。這包括當前正在執行的請求。last_request_end_timedatetime最近一次會話請求的完成時間。readsbigint在該會話期間該會話中的請求所執行的讀取次數。Writesbigint在該會話期間該會話中的請求所執行的寫入次數。logical_readsbigint已對該會話執行的邏輯讀取數。is_user_processbit如果會話是系統會話，則為 0。否則，為 1。text_sizeint會話的 TEXTSIZE 設置。languagenvarchar(128)會話的 LANGUAGE 設置。date_formatnvarchar(3)會話的 DATEFORMAT 設置。date_firstsmallint會話的 DATEFIRST 設置。quoted_identifierbit會話的 QUOTED_IDENTIFIER 設置。arithabortbit會話的 ARITHABORT 設置。ansi_null_dflt_onbit會話的 ANSI_NULL_DFLT_ON 設置。ansi_defaultsbit會話的 ANSI_DEFAULTS 設置。ansi_warningsbit會話的 ANSI_WARNINGS 設置。ansi_paddingbit會話的 ANSI_PADDING 設置。ansi_nullsbit會話的 ANSI_NULLS 設置。concat_null_yields_nullbit會話的 CONCAT_NULL_YIELDS_NULL 設置。transaction_isolation_levelsmallint會話的事務隔離級別。 0 = 未指定 1 = 未提交讀取 2 = 已提交讀取 3 = 可重復 4 = 可序列化 5 = 快照lock_timeoutint會話的 LOCK_TIMEOUT 設置。該值以毫秒計。deadlock_priorityint會話的 DEADLOCK_PRIORITY 設置。row_countbigint到目前為止會話返回的行數。prev_errorint會話返回的最近一個錯誤的 ID。比如說，我們要看那些主機有連接到了sqlserver伺服器，可以使用下面的sql語句： select distinct host_name from sys.dm_exec_Sessions要看那些用戶已連接到sqlserver伺服器： select distinct login_name from sys.dm_exec_Sessions當然，利用上面的列，我們可以獲得更多想要的客戶端信息2、Sys.dm_exec_connections 這個視圖返回了連接sqlserver伺服器上面的每個連接的詳細信息，下面是Sys.dm_exec_connections返回的列：列名數據類型 說明Session_idint標識與此連接關聯的會話。most_recent_Session_idint顯示與此連接關聯的最近請求的會話 ID。connect_timedatetime連接建立時的時間戳。net_transportnvarchar(40)說明該連接使用的物理傳輸協議。protocol_typenvarchar(40)指定負載的協議類型。此參數當前可區分 TDS (TSQL) 和 SOAP。protocol_versionint與此連接關聯的數據訪問協議的版本。endpoint_idint與此連接關聯的端點的唯一標識符。此 endpoint_id 可用於查詢 sys.endpoints 視圖。encrypt_optionnvarchar(40)說明是否為此連接啟用了加密的布爾值。auth_schemenvarchar(40)指定與此連接一起使用的 SQL Server/NT 身份驗證。node_affinitysmallint顯示與此連接關聯的 SOS 節點。num_readsint此連接中已發生的讀包次數。num_writesint此連接中已發生的寫數據包次數。last_readdatetime此連接中上一次發生讀操作的時間戳。last_writedatetime此連接中上一次發生寫操作的時間戳。net_packet_sizeint用於信息和數據的網路包的大小。client_net_addressvarchar(40)與此伺服器連接的客戶端的主機地址。client_tcp_portint與該連接關聯的客戶機上的埠號。local_net_addressvarchar(40)顯示此連接的目標伺服器的 IP 地址。只對使用 TCP 傳輸提供程序的連接可用。
㈦ 如何查看和獲取SQL Server實例名
一、查看實例名時可用
1、服務—SQL Server(實例名)，默認實例為(MSSQLSERVER)
或在連接企業管理時-查看本地實例
2、通過注冊表
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL
Server/InstalledInstance
3、用命令
sqlcmd/osql
sqlcmd -L
sqlcmd -Lc
osql
-L
獲取可用實例,以下舉一個例子，根據自己情況改
復制代碼
代碼如下:
DECLARE @Table TABLE ( instanceName
sysname NULL)
insert @Table EXEC sys.xp_cmdshell 'sqlcmd -Lc'
--LEFT(@@serverName,CHARINDEX('/',@@serverName+'/')-1) 替代為本機名就行了 ,
根據實例命名規則判斷
SELECT * FROM @Table WHERE instanceName LIKE LEFT( @@serverName , CHARINDEX
( '/' , @@serverName + '/' )- 1)+ '%'
二、
--1.
SELECT
SERVERPROPERTY('InstanceName')
--2
sp_helpserver
--3
select
@@SERVERNAME
--4
SELECT * FROM
SYS.SYSSERVERS
--5
SELECT * FROM
SYS.SERVERS
三、
EXECUTE xp_regread
@rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE/Microsoft/Microsoft SQL
Server/Instance Names/SQl',
@value_name='MSSQLSERVER'
四、
Select Case
When SERVERPROPERTY ('InstanceName') Is Null Then
@@SERVERNAME
Else SERVERPROPERTY ('InstanceName')
End
五、在本地或網路得到所有實例名
1、You can do with registry reading , like my code
復制代碼
代碼如下:
using System;
using Microsoft.Win32;
namespace SMOTest
{
class Program
{
static void
Main()
{
RegistryKey rk =
Registry.LocalMachine.OpenSubKey(@"SOFTWARE/Microsoft/Microsoft SQL
Server");
String[] instances =
(String[])rk.GetValue("InstalledInstances");
if (instances.Length
> 0)
{
foreach (String element in
instances)
{
if (element ==
"MSSQLSERVER")
Console.WriteLine(System.Environment.MachineName);
else
Console.WriteLine(System.Environment.MachineName + @"/"
+ element);
}
}
}
}
}
2、You can use SQLDMO.dll to retrieve the list of SQL
Server instances. The SQLDMO.dll can be found from the "C:/Program
Files/Microsoft SQL Server/80/Tools/Bin" folder. Refer this assembly in your
project and the following snippet would return a List Object containing the sql
server instances.
復制代碼
代碼如下:
public static List GetSQLServerInstances()
{
NameList sqlNameList = null;
Application app = null;
var sqlServers = new List();
try
{
app = new
ApplicationClass();
sqlNameList = app.ListAvailableSQLServers();
foreach
(string sqlServer in
sqlNameList)
sqlServers.Add(sqlServer);
}
catch(Exception
ex)
{
//play with the exception.
}
finally
{
if (sqlNameList
!= null)
sqlNameList = null;
if (app != null)
app =
null;
}
return sqlServers;
}
㈧ 獲取SQL Server伺服器列表的幾種方法
一、 SQL DMO描述：SQL Distributed Management Objects（SQL分布式管理對象），存在於SQLDMO.dll文件中，實際上是一個COM 對象，通過調用SQL DMO的ListAvailableSQLServers方法取得。速度：中調用示例：GetSQLServerList(ListBox1.items);代碼：uses ComObj; function GetSQLServerList(var AList: TStrings): Boolean;var SQLServerApp: Variant; ServerList: Variant; i: Integer;begin Result := True; try SQLServerApp := CreateOleObject('SQLDMO.Application'); ServerList := SQLServerApp.ListAvailableSQLServers; for i := 1 to ServerList.Count do AList.Add(ServerList.Item(i)); SQLServerApp := Unassigned; ServerList := Unassigned; except Result := False; end;end; 二、 NetServerEnum描述：網路服務函數，存在於NetApi32.dll文件中；通過NetServerEnum函數可取得裝有SQL Server服務端的計算機列表，只裝有SQL Server客戶端的計算機將不會被列舉其中；如果一台計算機的SQL Server服務剛剛啟動，那麼此函數將會過很久才能取到該計算機。
㈨ sql server 如何獲取當前操作的資料庫名
1、首先我們打開電腦里的sql server軟體。
