SQL Server/T-SQL/System/sysdepends

Getting Information About Your Functions

 

7> CREATE TABLE titles(
8>    title_id       varchar(20),
9>    title          varchar(80)       NOT NULL,
10>    type           char(12)          NOT NULL,
11>    pub_id         char(4)               NULL,
12>    price          money                 NULL,
13>    advance        money                 NULL,
14>    royalty        int                   NULL,
15>    ytd_sales      int                   NULL,
16>    notes          varchar(200)          NULL,
17>    pubdate        datetime          NOT NULL
18> )
19> 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> EXEC sp_helptext SalesByStore
4> GO
Text
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------



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>
2>
3> drop FUNCTION SalesByStore;
4> GO
1> drop table sales;
2> drop table titles;
3> GO