SQL Server/T-SQL Tutorial/System Tables Views/sysobjects — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 16:46, 26 мая 2010
Содержание
Check the existance of a table by querying sysobjects
<source lang="sql">
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</source>
Dropping all the triggers in the database using a cursor and dynamic execution.
<source lang="sql">
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></source>
Query sysobjects for user table, not system table
<source lang="sql">
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></source>
Retrieving all of the triggers for each table.
<source lang="sql">
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)</source>
sysobjects contains a row for each database object.
<source lang="sql">
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)</source>
