Lab 8.sql

SELECT db_name(dbid), filename 
FROM sys.sysaltfiles;


USE Airport_Norm;


/*Âûáðàòü èìåíà âñåõ òàáëèö, ñîçäàííûõ íàçíà÷åííûì ïîëüçîâàòåëåì áàçû äàííûõ*/
SELECT USER_NAME(ss.principal_id) AS 'User', st.name AS 'Name'
	FROM sys.tables st JOIN sys.schemas ss ON (ss.schema_id = st.schema_id)	
	WHERE (st.object_id NOT IN (SELECT major_id FROM sys.extended_properties));
GO
SELECT TABLE_NAME
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE';

/*Âûáðàòü èìÿ òàáëèöû, èìÿ ñòîëáöà òàáëèöû, ïðèçíàê òîãî, äîïóñêàåò ëè äàííûé ñòîëáåö
NULL-çíà÷åíèÿ, íàçâàíèå òèïà äàííûõ ñòîëáöà òàáëèöû, ðàçìåð ýòîãî òèïà äàííûõ - äëÿ âñåõ
òàáëèö, ñîçäàííûõ íàçíà÷åííûì ïîëüçîâàòåëåì áàçû äàííûõ è âñåõ èõ ñòîëáöîâ.*/

SELECT USER_NAME(ss.principal_id) AS 'user', so.name AS table_name, sc.name AS column_name, sc.is_nullable AS 'null',
	   st.name AS 'type_name', sc.max_length AS 'size' 
	FROM sys.objects so
		JOIN sys.columns sc ON (sc.object_id = so.object_id)
		JOIN sys.types st ON (sc.user_type_id = st.user_type_id)
		JOIN sys.schemas ss ON (ss.schema_id = so.schema_id)
	WHERE (so.type = 'U') AND (USER_NAME(ss.principal_id) ='dbo') AND (so.object_id NOT IN (SELECT major_id FROM sys.extended_properties));

/*Âûáðàòü íàçâàíèå îãðàíè÷åíèÿ öåëîñòíîñòè (ïåðâè÷íûå è âíåøíèå êëþ÷è), èìÿ òàáëèöû, â
êîòîðîé îíî íàõîäèòñÿ, ïðèçíàê òîãî, ÷òî ýòî çà îãðàíè÷åíèå ('PK' äëÿ ïåðâè÷íîãî êëþ÷à è 'F'
äëÿ âíåøíåãî) - äëÿ âñåõ îãðàíè÷åíèé öåëîñòíîñòè, ñîçäàííûõ íàçíà÷åííûì ïîëüçîâàòåëåì
áàçû äàííûõ.*/
SELECT USER_NAME(ss.principal_id) AS 'user', so1.name AS key_name, so2.name AS table_name, so1.type
	FROM sys.objects so1, sys.objects so2
		JOIN sys.schemas ss ON (ss.schema_id = so2.schema_id)
	WHERE (so1.parent_object_id = so2.object_id) AND ((so1.type = 'F') OR (so1.type = 'PK') OR (so1.type = 'C')) AND (USER_NAME(ss.principal_id) ='dbo')
	AND (so1.object_id NOT IN (SELECT major_id FROM sys.extended_properties)) AND (so2.object_id NOT IN (SELECT major_id FROM sys.extended_properties))
/*Âûáðàòü íàçâàíèå âíåøíåãî êëþ÷à, èìÿ òàáëèöû, ñîäåðæàùåé âíåøíèé êëþ÷, èìÿ òàáëèöû,
ñîäåðæàùåé åãî ðîäèòåëüñêèé êëþ÷ - äëÿ âñåõ âíåøíèõ êëþ÷åé, ñîçäàííûõ íàçíà÷åííûì
ïîëüçîâàòåëåì áàçû äàííûõ.*/
SELECT sfk.name AS kname, so1.name AS fname, so2.name AS kname, sc1.name AS fcolumn
	FROM sys.foreign_keys sfk
		JOIN sys.objects so1 ON (sfk.parent_object_id = so1.object_id)
		JOIN sys.objects so2 ON (sfk.referenced_object_id = so2.object_id)
		JOIN sys.schemas ss ON (ss.schema_id = so1.schema_id)
		JOIN sys.foreign_key_columns sfkc ON (sfkc.constraint_object_id = sfk.object_id)
		JOIN sys.columns sc1 ON (sfkc.parent_object_id = sc1.object_id AND sfkc.parent_column_id = sc1.column_id)
		
	WHERE (USER_NAME(ss.principal_id) ='dbo') AND (so1.object_id NOT IN (SELECT major_id FROM sys.extended_properties));
/*Âûáðàòü íàçâàíèå ïðåäñòàâëåíèÿ, SQL-çàïðîñ, ñîçäàþùèé ýòî ïðåäñòàâëåíèå - äëÿ âñåõ
ïðåäñòàâëåíèé, ñîçäàííûõ íàçíà÷åííûì ïîëüçîâàòåëåì áàçû äàííûõ.*/ 
SELECT v.name, text
	FROM sys.views v
		JOIN sys.syscomments c ON v.object_id = c.id
		JOIN sys.schemas ss ON (ss.schema_id = v.schema_id)
	WHERE (USER_NAME(ss.principal_id) ='dbo') AND (v.object_id NOT IN (SELECT major_id FROM sys.extended_properties));
/*Âûáðàòü íàçâàíèå òðèããåðà, èìÿ òàáëèöû, äëÿ êîòîðîé îïðåäåëåí òðèããåð - äëÿ âñåõ
òðèããåðîâ, ñîçäàííûõ íàçíà÷åííûì ïîëüçîâàòåëåì áàçû äàííûõ.*/
SELECT strr.name, st.name
	FROM sys.triggers strr
	JOIN sys.tables st ON st.object_id = strr.parent_id
	JOIN sys.schemas ss ON (ss.schema_id = st.schema_id)
	WHERE (USER_NAME(ss.principal_id) ='dbo') AND (strr.object_id NOT IN (SELECT major_id FROM sys.extended_properties));