SQL Server/T-SQL Tutorial/System Tables Views/sysobjects — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:46, 26 мая 2010
Содержание
Check the existance of a table by querying sysobjects
4> IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id("Shippers")
5> AND OBJECTPROPERTY(id, "IsUserTable") = 1)
6> DROP TABLE Shippers
7> GO
1>
2> CREATE TABLE Shippers(
3> ShipperID int IDENTITY (1, 1) NOT NULL,
4> CompanyName nvarchar (40) NOT NULL,
5> Phone nvarchar (24) NULL
6> )
7> GO
1>
2> drop table Shippers;
3> GO
Dropping all the triggers in the database using a cursor and dynamic execution.
4>
5>
6> DECLARE cuTriggers CURSOR
7> FOR
8> SELECT name FROM sysobjects WHERE type = "TR"
9> OPEN cuTriggers
10> DECLARE @chvTrigger VARCHAR(30)
11> FETCH NEXT FROM cuTriggers INTO @chvTrigger
12>
13> WHILE (@@fetch_status <> -1)
14> BEGIN
15> EXEC ("DROP TRIGGER " + @chvTrigger)
16>
17> FETCH NEXT FROM cutriggers INTO @chvTrigger
18>
19> END
20> DEALLOCATE cuTriggers
21> GO
1>
Query sysobjects for user table, not system table
3> EXEC sp_addrole "db_dataupdaters"
4> GO
1> DECLARE tables_curs CURSOR FOR
2> SELECT name FROM sysobjects
3> WHERE type="U"
4> OPEN tables_curs
5> DECLARE @tablename varchar(30), @output_msg varchar(80)
6> FETCH NEXT FROM tables_curs INTO @tablename
7> WHILE (@@FETCH_STATUS=0 )
8> BEGIN
9> EXEC ("GRANT UPDATE ON " + @tablename
10> + " TO db_dataupdaters")
11> IF (@@ERROR=0)
12> SELECT @output_msg=
13> "UPDATE permission granted on table "
14> + @tablename
15> ELSE
16> SELECT @output_msg=
17> "Failed to grant UPDATE permission on table "
18> + @tablename + " @@ERROR=" +
19> CONVERT(varchar, @@ERROR)
20> PRINT @output_msg
21> FETCH NEXT FROM tables_curs INTO @tablename
22> END
23> CLOSE tables_curs
24> DEALLOCATE tables_curs
25>
26> GO
UPDATE permission granted on table pub_info
UPDATE permission granted on table authors_CS
UPDATE permission granted on table spt_fallback_db
UPDATE permission granted on table spt_fallback_dev
UPDATE permission granted on table spt_fallback_usg
UPDATE permission granted on table titleauthor
UPDATE permission granted on table department_pivot
UPDATE permission granted on table BankerBalances
UPDATE permission granted on table SalesMw
UPDATE permission granted on table BillingCopy
UPDATE permission granted on table spt_monitor
UPDATE permission granted on table spt_values
UPDATE permission granted on table MSreplication_options
UPDATE permission granted on table OldBillings
UPDATE permission granted on table Table1
UPDATE permission granted on table Table2
1>
2>
Retrieving all of the triggers for each table.
13>
14>
15> SELECT name,
16> COALESCE(OBJECT_NAME(instrig), "N/A") AS InsertTrigger,
17> COALESCE(OBJECT_NAME (updtrig), "N/A") AS UpdateTrigger,
18> COALESCE(OBJECT_NAME (deltrig), "N/A") AS DeleteTrigger
19> FROM sysobjects
20> WHERE type = "U"
21> ORDER BY name
22> GO
name InsertTrigger
UpdateTrigger DeleteTrigger
-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------
--------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------
-------------------------------------------------------------------------------------------------------------------
MSreplication_options N/A
N/A N/A
department_pivot N/A
N/A N/A
SalesMw N/A
N/A N/A
spt_fallback_db N/A
N/A N/A
spt_fallback_dev N/A
N/A N/A
spt_fallback_usg N/A
N/A N/A
spt_monitor N/A
N/A N/A
spt_values N/A
N/A N/A
(8 rows affected)
sysobjects contains a row for each database object.
type The type of a database object.
Can be one of the following strings:
C = check constraint;
D = default;
F = foreign key constraint;
L = transaction log;
P = stored procedure;
K = primary key or unique constraint;
R rule;
RF = replication stored procedure;
S = system table;
TR = trigger;
U = user table;
V = view;
X = extended stored procedure
21> select top 10 * from sysobjects;
22> GO
name id xtype uid info status base_schema_ver replinfo
parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category
cache
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----- ------ ------ ----------- --------------- -----------
----------- ----------------------- ------- ----------- ---------------- ---- -------- ------- -------- ----------------------- ----------- ----------- ----------- ----------- ----------- -----------
------
sysrowsetcolumns 4 S 4 0 0 0 0
0 2005-10-14 01:36:06.707 0 0 0 S 1 1 0 2005-10-14 01:36:06.707 0 0 0 0 0 2
0
sysrowsets 5 S 4 0 0 0 0
0 2005-10-14 01:36:06.690 0 0 0 S 1 1 0 2005-10-14 01:36:06.690 0 0 0 0 0 2
0
sysallocunits 7 S 4 0 0 0 0
0 2005-10-14 01:36:06.690 0 0 0 S 1 1 0 2005-10-14 01:36:06.690 0 0 0 0 0 2
0
sysfiles1 8 S 4 0 0 0 0
0 2003-04-08 09:13:37.267 0 0 0 S 1 1 0 2003-04-08 09:13:37.267 0 0 0 0 0 2
0
syshobtcolumns 13 S 4 0 0 0 0
0 2005-10-14 01:36:06.707 0 0 0 S 1 1 0 2005-10-14 01:36:06.707 0 0 0 0 0 2
0
syshobts 15 S 4 0 0 0 0
0 2005-10-14 01:36:06.707 0 0 0 S 1 1 0 2005-10-14 01:36:06.707 0 0 0 0 0 2
0
sysftinds 25 S 4 0 0 0 0
0 2005-10-14 01:36:06.877 0 0 0 S 1 1 0 2005-10-14 01:36:06.877 0 0 0 0 0 2
0
sysserefs 26 S 4 0 0 0 0
0 2005-10-14 01:36:06.720 0 0 0 S 1 1 0 2005-10-14 01:36:06.720 0 0 0 0 0 2
0
sysowners 27 S 4 0 0 0 0
0 2005-10-14 01:36:06.863 0 0 0 S 1 1 0 2005-10-14 01:36:06.863 0 0 0 0 0 2
0
sysdbreg 28 S 4 0 0 0 0
0 2005-10-14 01:36:06.737 0 0 0 S 1 1 0 2005-10-14 01:36:06.737 0 0 0 0 0 2
0
(10 rows affected)