SQL Server/T-SQL Tutorial/System Tables Views/sysdepends

Материал из SQL эксперт
Версия от 13:24, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Join sysdepends and syscolumns

   <source lang="sql">

4> CREATE TABLE titles( 5> title_id varchar(20), 6> title varchar(80) NOT NULL, 7> type char(12) NOT NULL, 8> pub_id char(4) NULL, 9> price money NULL, 10> advance money NULL, 11> royalty int NULL, 12> ytd_sales int NULL, 13> notes varchar(200) NULL, 14> pubdate datetime NOT NULL 15> ) 16> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE sales( 3> stor_id char(4) NOT NULL, 4> ord_num varchar(20) NOT NULL, 5> ord_date datetime NOT NULL, 6> qty smallint NOT NULL, 7> payterms varchar(12) NOT NULL, 8> title_id varchar(80) 9> ) 10> GO 1> insert sales values("1", "QA7442.3", "09/03/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/04/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/04/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/04/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/04/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/04/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> Create FUNCTION SalesByStore(@storid varchar(30)) 4> RETURNS TABLE 5> AS 6> RETURN (SELECT title, qty 7> FROM dbo.sales s, dbo.titles t 8> WHERE s.stor_id = @storid AND t.title_id = s.title_id) 9> GO 1> 2> 3> ALTER FUNCTION SalesByStore(@storid varchar(30)) 4> RETURNS TABLE 5> WITH SCHEMABINDING 6> AS 7> RETURN (SELECT title, qty 8> FROM dbo.sales s, dbo.titles t 9> WHERE s.stor_id = @storid AND t.title_id = s.title_id) 10> GO 1> 2> 3> SELECT obj_name = SUBSTRING(OBJECT_NAME(d.id), 1, 20), 4> dep_obj = SUBSTRING(OBJECT_NAME(d.depid), 1, 20), 5> col_name = SUBSTRING(name, 1, 15), 6> IsSchemaBound = CASE deptype 7> WHEN 1 THEN "Schema Bound" 8> ELSE "Free" 9> END 10> FROM 11> sysdepends d 12> JOIN syscolumns c ON d.depid = c.id 13> AND d.depnumber = c.colid 14> WHERE object_name(d.id) LIKE "SalesByStore%" 15> GO obj_name dep_obj col_name IsSchemaBound

-------------------- --------------- -------------

SalesByStore titles title_id Schema Bound SalesByStore titles title Schema Bound SalesByStore sales stor_id Schema Bound SalesByStore sales qty Schema Bound SalesByStore sales title_id Schema Bound (5 rows affected) 1> 2> 3> 4> SELECT routine_definition 5> FROM INFORMATION_SCHEMA.routines 6> WHERE routine_name = "SalesByStore" 7> GO routine_definition






CREATE FUNCTION SalesByStore(@storid varchar(30)) RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT title, qty 

       FROM dbo.sales s, dbo.titles t
       WHERE s.stor_id = @storid AND t.title_id = s.title_id)

(1 rows affected) 1> 2> 3> 4> drop FUNCTION SalesByStore; 5> GO 1> drop table sales; 2> drop table titles; 3> GO 1></source>


sysdepends contains a row for every dependency relationship between tables, views, and stored procedures. It appears in the master database and in every user-defined database.

   <source lang="sql">

3> 4> select top 10 * from sysdepends; 5> GO id depid number depnumber status deptype depdbid depsiteid selall resultobj readobj

----------- ------ --------- ------ ------- ------- --------- ------ --------- -------

(0 rows affected)</source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/System_Tables_Views/sysdepends&oldid=6910»