Az SqlServer belső információs világa
Az SQL Server rendszer- és adatkezelési nézetei ugyanazokat az információkat nyújtják, mint az információs sémanézetek, és sokkal több, az SQL Serverre jellemző egyedi információt. Ha belenéz a színfalak mögé az INFORMATION_SCHEMA nézeteibe sok információ kerül elő.
Információ az adatbázisban található táblákról
Minta lekérdezés speciális megvalósítása
Nézzük meg az INFORMATION_SCHEMA.TABLES nézet definícióját példaként
--tsql
DECLARE @srcCode VARCHAR(max)
SELECT @srcCode =
OBJECT_DEFINITION(object_id('INFORMATION_SCHEMA.tables'))
PRINT @srcCode
DECLARE @srcCode VARCHAR(max)
SELECT @srcCode =
OBJECT_DEFINITION(object_id('INFORMATION_SCHEMA.tables'))
PRINT @srcCode
Ezek a nézetek (és tartalmuk) az SQL Server Management Studio (SSMS) Rendszernézetek listája alatt virtualizálva találhatóak meg.
Különböző sémák alatt található ugyanazon tábla névvel rendelkező objektumok listázása
--tsql
SELECT Table_Name,count(*) as Owners
FROM [INFORMATION_SCHEMA].[tables]
GROUP BY Table_Name HAVING count(*)>1
Megszorítások a táblákon
A megszorítások egy adatbázistábla oszlopaira alkalmazott szabályok. Általában korlátozzák az oszlopokhoz hozzáadható adatok típusát.
A TABLE_CONSTRAINTS nézet felsorolja a táblákat és a táblákon lévő megszorításokat.
--tsql
SELECT Table_Name,count(*) FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]
A CHECK kényszerdefiníciók a CHECK_CONSTRAINTS nézetben találhatók , amely tartalmazza a CHECK_CLAUSE oszlopot a megszorítás meghatározásához.
A fennmaradó kényszertípusok (FOREIGN KEY, PRIMARY KEY és EGYEDI) referenciatáblázatok és oszlopok, és a CONSTRAINT_COLUMN_USAGE nézetben találhatók .
Egyben így lehet használni a megszorítások lekérdezését:
--tsql
SELECT constraint_type AS TYPE,TC.constraint_name AS NAME
CASE
WHEN tc.constraint_type = 'CHECK'
THEN cc.check_clause
ELSE cu.table_name+'.'+cu.column_Name COLLATE DATABASE_DEFAULT
END AS EXPRESSION
FROM INFORMATION_SCHEMA.table_constraints tc
LEFT JOIN INFORMATION_SCHEMA.check_constraints cc
ON cc.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
AND tc.CONSTRAINT_TYPE='CHECK'
LEFT JOIN INFORMATION_SCHEMA.constraint_column_usage cu
ON cu.constraint_name=tc.CONSTRAINT_NAME
ORDER BY constraint_type,tc.constraint_name
Hasznos nézetek az információs sémából
VIEWS --- az oszlopneveket ebben TABLE prefixu elnevű, bár a tartalom maga a nézet.
VIEW_TABLE_USAGE
COLUMNS
DOMAIN
column_domain_usage
ROUTINES
ROUTINE_COLUMNS --- Ha a rutin táblaértékű függvény (egyetlen érték helyett táblát ad vissza),
PARAMETERS
permissions
principals
Táblák nézetek, ... nézetek nagy része a sys.objects-ből származik. Az SQL adatbázisban minden
„objektumhoz” (táblák, eljárások, triggerek stb.) tartozik egy objektumazonosító, és a sys
objektumok tárolják az objektumról szóló információkat. Az OBJECT_NAME() SQL függvény egy
objektumnevet ad vissza az object_id paraméterből.
Nézetekben használt/hivatkozott táblák lekérdezése
---tsql
SELECT v.table_schema,v.table_name AS 'ViewName',
IsNull(xx.RefObjects,'') AS 'References'
FROM INFORMATION_SCHEMA.views v
LEFT JOIN(Select distinct ST2.view_schema + '.' +
ST2.view_name as ViewRollup,
ltrim(substring(( Select ', ' + ST1.table_name
AS[text()]
FROM INFORMATION_SCHEMA.view_table_usage ST1
WHERE ST1.view_schema + '.' + ST1.view_name = ST2.view_schema +
'.' + ST2.view_name
ORDER BY ST1.view_schema + '.' + ST1.view_name
FOR XML PATH('') ), 2, 8000))[RefObjects]
FROM INFORMATION_SCHEMA.view_table_usage ST2
) xx ON xx.ViewRollup = v.table_schema + '.' + v.table_name
ORDER BY v.table_schema,v.table_name
'DATUM' mezőneveket tartalmazó tábla lista
---tsql
SELECT table_schema,table_name
FROM INFORMATION_SCHEMA.columns
WHERE column_name='DATUM'
ORDER BY table_schema,table_name
Az adatbázisban előforduló ugyan olyan nevű de eltérő típusú vagy hosszú mezők listázása
---tsql
SELECT column_name,
CASE
WHEN min(data_type)<>max(data_type) THEN 'Type mismatch'
ELSE ''
END AS TypeError,
CASE
WHEN min(character_maximum_length) <> max(character_maximum_length) THEN
'Size differences'
ELSE ''
END AS SizeError,count(*) AS NumTables
FROM INFORMATION_SCHEMA.columns
GROUP BY column_name
HAVING (min(data_type)<>max(data_type) )
OR (min(character_maximum_length) <> max(character_maximum_length))
Hasonló nevű mezők keresése ('BEVETEL')
--- tsql
SELECT table_schema,table_Name,column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_Name LIKE '%BEVETEL%'
Ezzel a lekérdezéssel ellenőrizheti a tárolt eljárásokat és függvényeket, és megnézheti, hogy milyen paramétereket várnak el (sorrendben).
---tsql
SELECT r.routine_type,r.specific_name,rc.parameter_name
FROM INFORMATION_SCHEMA.routines r
JOIN INFORMATION_SCHEMA.parameters rc ON r.specific_name=rc.specific_name
WHERE rc.parameter_mode='IN'
ORDER BY r.specific_name,rc.ordinal_position
Adatbázis szerver rendszer szintű katalógus adatok nézetei
Ezek használatához/eléréséhez/lekérdezéshez általában a VIEW SERVER STATE engedély kell
dm_os_host_info
dm_os_windows_info
dm_os_sys_memory
dm_os_enumerate_fixed_drives
dm_os_enumerate_file_system
dm_server_registry
master_files
Használat példák:
---tsql
SELECT * FROM [sys].[dm_server_registry]
-- tsql : Server adatbázisok és virzió információk
---------------------------------------------
SELECT database_id,[name],create_date,
CASE compatibility_level
WHEN 80 THEN 'SQL 2005'
when 90 then 'SQL 2005'
when 100 then 'SQL 2008'
when 110 then 'SQL 2012'
when 120 then 'SQL 2014'
when 130 then 'SQL 2016'
when 140 then 'SQL 2017'
when 150 then 'SQL 2019'
else 'Unknown version'
end as SQL_Level
from sys.databases
order by database_id --- A db_id() függvény az aktuális adatbázis numerikus azonosítóját adja vissza
--- tsql : adatbázis filek listája
SELECT type_desc,name,physical_name FROM sys.database_files
Rendszer globális változói
@@version globális változó használata . Ez egy karakterláncot ad vissza, amely tartalmazza a sqlserver verziót és a szerzői jogi információkat.
SERVERPROPERTY('ProductMajorVersion') --- a SERVERPROPERTY függvényt, és lekérheti a ProjectMajorVersion-t,
Szabad terület, valamint rendszer- és adatbázisfájlok megjelenítése
--- tsql
IF object_id('sys.dm_os_enumerate_fixed_drives') is not null
SELECT mf.type_desc,mf.name,mf.physical_name,
fd.drive_type_desc,fd.free_space_in_bytes/(1024*1024)
as MB_Free
FROM sys.master_files mf
JOIN sys.dm_os_enumerate_fixed_drives fd
on substring(mf.physical_name,1,3)=fd.fixed_drive_path
WHERE database_id in (1,2,db_id())
ELSE
BEGIN
create table #tmpDrives (drive char(1),free_space_in_bytes
bigint)
INSERT INTO #tmpDrives
exec xp_fixeddrives
SELECT mf.type_desc,mf.name,mf.physical_name,
'FIXED' as
drive_type_desc,round((fd.free_space_in_bytes*1.0)/1024,0) as MB_Free
FROM sys.master_files mf
JOIN #tmpDrives fd on
substring(mf.physical_name,1,1)=fd.drive
WHERE database_id in (1,2,db_id())
DROP TABLE #tmpDrives
END
Hasznos műveletek
adatbázisok összehasonlítása
---tsql
DECLARE @targetDB varchar(128)
SET @targetDB='' -- SET NAME HERE
CREATE TABLE #tmpProps (PropertyName varchar(100))
INSERT INTO #tmpProps values ('LCID'),('Collation'),
('UserAccess'),('IsArithmeticAbortEnabled'), ('IsNullConcat')
SELECT PropertyName,
DATABASEPROPERTYEX(db_name(),PropertyName) as CurrentDB_Property,
DATABASEPROPERTYEX(@targetDB,PropertyName) as TargetDB_Property
FROM #tmpProps
DROP TABLE #tmpProps
Kiknem van módosítási joga az adatbázisban
---tsql
SELECT
pr.name,pr.type,p.permission_name
FROM sys.database_permissions p
JOIN sys.database_principals pr ON pr.principal_id=p.grantee_principal_id
WHERE permission_name IN ('DELETE','UPDATE','INSERT') AND state='G'
Ez a lista azokat a táblázatokat jeleníti meg, amelyek tartalmaznak egy Identity oszlopot, valamint az oszlop kezdőértékét és növekményét. Ha a táblázatban vannak sorok, az Utolsó_érték oszlop az utolsó magszámot jelenti.
--tsql
SELECT
OBJECT_SCHEMA_NAME(st.object_id)+'.'+st.name AS [TableName],
ic.name AS KeyName,
t.name AS dataType,
ic.seed_value,ic.increment_value,
isNull(ic.last_value,0) AS Last_Value
FROM sys.tables st
JOIN sys.identity_columns ic ON ic.object_id=st.object_id
JOIN sys.types t ON t.system_type_id=ic.system_type_id
ORDER BY [TableName]
Megjegyzések
Megjegyzés küldése