32	fix_all_ident	GO
33	fix_all_ident	CREATE PROCEDURE #fix_all_ident AS
34	fix_all_ident	DECLARE @objname VARCHAR(30),
35	fix_all_ident	        @objid INT,
36	fix_all_ident	        @lastobjid INT,
37	fix_all_ident	        @cmd VARCHAR(80)
38	fix_all_ident	SET NOCOUNT ON
39	fix_all_ident	SELECT @objid = 1
40	fix_all_ident	SELECT @lastobjid = 0
41	fix_all_ident	WHILE @objid IS NOT NULL
42	fix_all_ident	  BEGIN
43	fix_all_ident	    SELECT @objid = (SELECT MIN(id) FROM sysobjects
44	fix_all_ident	                     WHERE id > @lastobjid
45	fix_all_ident	                     AND type = "U")
46	fix_all_ident	    SELECT @objname = OBJECT_NAME(@objid)
47	fix_all_ident	    IF (SELECT c.name
48	fix_all_ident	        FROM syscolumns c, systypes t
49	fix_all_ident	        WHERE c.id = @objid
50	fix_all_ident	        AND c.usertype *= t.usertype
51	fix_all_ident	        AND (c.status & 128) = 128) IS NOT NULL
52	fix_all_ident	          BEGIN
53	fix_all_ident	            SELECT @cmd = "DBCC CHECKIDENT(" + @objname + ")"
54	fix_all_ident	            EXEC (@cmd)
55	fix_all_ident	          END
56	fix_all_ident	    SELECT @lastobjid = @objid
57	fix_all_ident	  END
58	fix_all_ident	GO
59	fix_all_ident	EXEC #fix_all_ident
95	script_check_space	DECLARE @dbid INT,
96	script_check_space	        @dbSize dec(15,0),
97	script_check_space	        @reserved dec(15,0),
98	script_check_space	        @used dec(15,0),
99	script_check_space	        @percentFull INT,
100	script_check_space	        @msg VARCHAR(255),
101	script_check_space		     @qry VARCHAR(255),
102	script_check_space	        @dbName VARCHAR(30),
103	script_check_space	        @mailTo VARCHAR(30)
104	script_check_space	SET NOCOUNT ON
105	script_check_space	SELECT @dbName = DB_NAME()
106	script_check_space	SELECT @mailTo = (SELECT emailAddress FROM persons WHERE role='DBA')
107	script_check_space	SELECT @dbid = DB_ID(@dbName)
108	script_check_space	SELECT @dbSize = SUM(CONVERT(DEC(15),size))*2
109	script_check_space	FROM master..sysusages
110	script_check_space	WHERE dbid = DB_ID(@dbName)
111	script_check_space	SELECT @used = SUM(CONVERT(DEC(15),used))*2
112	script_check_space	FROM sysindexes
113	script_check_space	WHERE indid IN (0, 1, 255)
114	script_check_space	SELECT @percentFull = (CONVERT(INT, (1 - (@dbSize - @used)/@dbSize)*100))
115	script_check_space	IF @percentFull > 90
116	script_check_space	  BEGIN
117	script_check_space	    SELECT @msg = "Database " + @dbName +
118	script_check_space	                  " is " + CONVERT(VARCHAR(2),@percentFull) + "% Full!"
119	script_check_space	    SELECT @qry = "USE " + @dbName + " EXEC sp_spaceused"
120	script_check_space	    EXEC master..xp_sendmail
121	script_check_space	             @recipients = @mailto,
122	script_check_space	             @subject = "DATABASE SPACE WARNING",
123	script_check_space	             @message = @msg,
124	script_check_space	             @query = @qry
125	script_check_space	  END
126	script_check_space	SELECT "database " + @dbName +
127	script_check_space	       CHAR(10) + CHAR(13) +
128	script_check_space	       "Total Space Available = " + CONVERT(VARCHAR(10), @dbSize) + " KB" +
129	script_check_space	       CHAR(10) + CHAR(13) +
130	script_check_space	      "Space Used = " + CONVERT(VARCHAR(10), @used) + " KB" +
131	script_check_space	       CHAR(10) + CHAR(13) +
132	script_check_space	      "Percent Full = " + CONVERT(VARCHAR(3), @percentFull) + " %"
133	script_devs_and_dbs	DECLARE @name VARCHAR(30),
134	script_devs_and_dbs	        @phyname VARCHAR(127),
135	script_devs_and_dbs		@vdevno VARCHAR(20),
136	script_devs_and_dbs		@size VARCHAR(20),
137	script_devs_and_dbs		@line varchar(80)
138	script_devs_and_dbs	SET NOCOUNT ON
139	script_devs_and_dbs	PRINT "/**************************************************************"
140	script_devs_and_dbs	PRINT ""
141	script_devs_and_dbs	PRINT "          SQL SERVER DEVICE AND DATABASE RECOVERY SCRIPT"
142	script_devs_and_dbs	PRINT ""
143	script_devs_and_dbs	SELECT @line = "                     SERVER: " + @@SERVERNAME
144	script_devs_and_dbs	PRINT @line
145	script_devs_and_dbs	PRINT ""
146	script_devs_and_dbs	SELECT @line = "                 DATE: " + CONVERT(CHAR(20),getdate())
147	script_devs_and_dbs	PRINT @line
148	script_devs_and_dbs	PRINT ""
149	script_devs_and_dbs	PRINT "          =================== NOTES =================="
150	script_devs_and_dbs	PRINT ""
151	script_devs_and_dbs	PRINT "   This script will create all SQL Server devices and databases."
152	script_devs_and_dbs	PRINT ""
153	script_devs_and_dbs	PRINT "   Rebuild any database by making sure a device structure like that"
154	script_devs_and_dbs	PRINT "   shown exists for that database, running the relevant create"
155	script_devs_and_dbs	PRINT "   script(s), and loading the most recent dump for that database."
156	script_devs_and_dbs	PRINT "                 "
157	script_devs_and_dbs	PRINT "   The device section includes a script to create the master device."
158	script_devs_and_dbs	PRINT "   Remove the script if you are working with a good master device"
159	script_devs_and_dbs	PRINT "   that matches the size shown."
160	script_devs_and_dbs	PRINT ""
161	script_devs_and_dbs	PRINT "   The database section includes scripts to create the master, model,"
162	script_devs_and_dbs	PRINT "   msdb, pubs, and tempdb databases. Remove as necessary BEFORE "
163	script_devs_and_dbs	PRINT "   running the full script."
164	script_devs_and_dbs	PRINT ""
165	script_devs_and_dbs	PRINT "   To restart after a rebuild of master where all user devices"
166	script_devs_and_dbs	PRINT "   are still useable, change all DISK INIT to DISK REINIT and"
167	script_devs_and_dbs	PRINT "   replace the entire database create section with the single"
168	script_devs_and_dbs	PRINT "   line: DISK REFIT"
169	script_devs_and_dbs	PRINT "*****************************************************************/"
170	script_devs_and_dbs	PRINT ""
171	script_devs_and_dbs	PRINT "         /*============= DEVICES SCRIPTS ===============*/"
172	script_devs_and_dbs	PRINT "USE master"
173	script_devs_and_dbs	PRINT "GO"
174	script_devs_and_dbs	DECLARE object_cursor CURSOR
175	script_devs_and_dbs	FOR
176	script_devs_and_dbs	 SELECT d.name,
177	script_devs_and_dbs	        phyname,
178	script_devs_and_dbs	        STR(CONVERT(TINYINT,SUBSTRING(CONVERT(BINARY(4),d.low),v.low, 1)),3,0),
179	script_devs_and_dbs	        str(d.high - d.low + 1,8,0)
180	script_devs_and_dbs	FROM master..sysdevices d,master..spt_values v
181	script_devs_and_dbs	WHERE cntrltype=0 AND v.type="E" AND v.number=3
182	script_devs_and_dbs	ORDER BY CONVERT(TINYINT,SUBSTRING(CONVERT(BINARY(4),d.low),v.low,1))
183	script_devs_and_dbs	OPEN object_cursor
184	script_devs_and_dbs	FETCH NEXT FROM object_cursor INTO @name, @phyname, @vdevno, @size
185	script_devs_and_dbs	WHILE (@@fetch_status <> -1)
186	script_devs_and_dbs	  BEGIN
187	script_devs_and_dbs	  IF @name = "master" PRINT "/*****"
188	script_devs_and_dbs	    PRINT " "
189	script_devs_and_dbs	    PRINT "DISK INIT"
190	script_devs_and_dbs	    SELECT @line = "   NAME = " + CHAR(39) + @name + CHAR(39) + CHAR(44)
191	script_devs_and_dbs	    PRINT @line
192	script_devs_and_dbs	    SELECT @line = "   PHYSNAME = " + CHAR(39) + @phyname + CHAR(39) + CHAR(44)
193	script_devs_and_dbs	    PRINT @line
194	script_devs_and_dbs	    SELECT @line = "   VDEVNO = " + @vdevno + CHAR(44)
195	script_devs_and_dbs	    PRINT @line
196	script_devs_and_dbs	    SELECT @line = "   SIZE = " + @size
197	script_devs_and_dbs	    PRINT @line
198	script_devs_and_dbs	    PRINT ""
199	script_devs_and_dbs	    IF @name = "master" PRINT "*****/"
200	script_devs_and_dbs	    PRINT "GO"
201	script_devs_and_dbs	    PRINT ""
202	script_devs_and_dbs	    FETCH NEXT FROM object_cursor INTO @name, @phyname, @vdevno, @size
203	script_devs_and_dbs	  END
204	script_devs_and_dbs	DEALLOCATE object_cursor
205	script_devs_and_dbs	PRINT "          /*============ DATABASES SCRIPTS ==============*/"
206	script_devs_and_dbs	EXEC sp_help_revdatabase
207	script_devs_and_dbs	PRINT "          /*================== END ======================*/"
1496	script_drop_alias	DECLARE @rowid INT,
1497	script_drop_alias	        @CMD VARCHAR(60)
1498	script_drop_alias	SELECT @rowid = (SELECT MIN(suid) FROM sysalternates)
1499	script_drop_alias	WHILE @rowid IS NOT NULL
1500	script_drop_alias	BEGIN
1501	script_drop_alias	SELECT @CMD = "sp_dropalias " + SUSER_NAME(@rowid)
1502	script_drop_alias	SELECT @CMD
1503	script_drop_alias	EXEC (@CMD)
1504	script_drop_alias	SELECT @rowid = (SELECT MIN(suid) FROM sysalternates
1505	script_drop_alias	                  WHERE suid > @rowid)
1506	script_drop_alias	END
1546	script_drop_users	DECLARE @rowid INT,
1547	script_drop_users	        @CMD VARCHAR(60)
1548	script_drop_users	/* remove all users except dbo and guest */
1549	script_drop_users	SELECT @rowid = (SELECT MIN(uid) FROM sysusers
1550	script_drop_users	                 WHERE suid > 1)
1551	script_drop_users	WHILE @rowid IS NOT NULL
1552	script_drop_users	  BEGIN
1553	script_drop_users	    SELECT @CMD = "sp_dropuser " + USER_NAME(@rowid)
1554	script_drop_users	    SELECT @CMD
1555	script_drop_users	    EXEC (@CMD)
1556	script_drop_users	    SELECT @rowid = (SELECT MIN(uid) FROM sysusers
1557	script_drop_users	                     WHERE uid > @rowid AND suid > 1)
1558	script_drop_users	  END
208	script_permissions	GO
209	script_permissions	DECLARE @number INT,
210	script_permissions	        @name VARCHAR(35),
211	script_permissions	        @database VARCHAR(30),
212	script_permissions		@line VARCHAR(80),
213	script_permissions	        @cmd VARCHAR(255)
214	script_permissions	SET NOCOUNT ON
215	script_permissions	SELECT @database = (SELECT name FROM master..sysdatabases
216	script_permissions		            WHERE dbid = (SELECT dbid FROM master..sysprocesses
217	script_permissions		     		          WHERE spid = @@SPID))
218	script_permissions	SELECT @line = "/* All object permissions for database " + @database + " */"
219	script_permissions	PRINT @line
220	script_permissions	SELECT @line = "/* " + CONVERT(VARCHAR(20) ,getdate()) + " */"
221	script_permissions	PRINT @line
222	script_permissions	SELECT @line = "USE " + @database
223	script_permissions	PRINT @line
224	script_permissions	PRINT ""
225	script_permissions	PRINT "GO"
226	script_permissions	PRINT ""
227	script_permissions	SELECT @number = (SELECT MIN(number) FROM master..spt_values
228	script_permissions	                  WHERE type = "O"
229	script_permissions	                  AND name NOT LIKE "%system%"
230	script_permissions	                  AND name NOT LIKE "%replication%"
231	script_permissions	                  AND number > 0)
232	script_permissions	WHILE (@number <= (SELECT MAX(number) FROM master..spt_values
233	script_permissions	                   WHERE type = "O"))
234	script_permissions	       AND (@number IS NOT NULL)
235	script_permissions	  BEGIN
236	script_permissions	    SELECT @name = (SELECT name FROM master..spt_values
237	script_permissions	                    WHERE number = @number
238	script_permissions	                    AND type = "O")
239	script_permissions	    SELECT @line = " Permissions on all " + @name + "s"
240	script_permissions	    PRINT ""
241	script_permissions	    PRINT "/*********************************"
242	script_permissions	    PRINT @line
243	script_permissions	    PRINT " *********************************/"
244	script_permissions	    SELECT "GRANT " + RTRIM(s.name) + " ON " + RTRIM(o.name) + " TO " + u.name
245	script_permissions	    FROM sysprotects p, sysusers u,  sysobjects o,
246	script_permissions	         master..spt_values s, master..spt_values v
247	script_permissions	    WHERE p.action = s.number
248	script_permissions	    AND s.type = "T"
249	script_permissions	    AND p.uid = u.uid
250	script_permissions	    AND o.id = p.id
251	script_permissions	    AND v.number = sysstat & 0xf
252	script_permissions	    AND v.number = @number
253	script_permissions	    AND v.type = "O"
254	script_permissions	    ORDER BY v.number,s.name
255	script_permissions	    IF @@ROWCOUNT = 0
256	script_permissions	      BEGIN
257	script_permissions	        PRINT ""
258	script_permissions	        PRINT "-- NONE --"
259	script_permissions	        PRINT ""
260	script_permissions	      END
261	script_permissions	    SELECT @number = (SELECT MIN(number) FROM master..spt_values
262	script_permissions	                      WHERE type = "O"
263	script_permissions	                      AND name NOT LIKE "%system%"
264	script_permissions	                      AND name NOT LIKE "%replication%"
265	script_permissions	                      AND number > @number)
266	script_permissions	  END
267	script_permissions	PRINT ""
268	script_permissions	PRINT "GO"
269	script_permissions	PRINT ""
1675	script_reindex	DECLARE @objectname varchar(30)
1676	script_reindex	DECLARE @CMD varchar(255)
1677	script_reindex	SET NOCOUNT ON
1678	script_reindex	DECLARE object_cursor CURSOR
1679	script_reindex	FOR
1680	script_reindex	SELECT name FROM sysobjects
1681	script_reindex	WHERE type = "U"
1682	script_reindex	ORDER BY name
1683	script_reindex	OPEN object_cursor
1684	script_reindex	FETCH NEXT FROM object_cursor INTO @objectname
1685	script_reindex	WHILE (@@fetch_status <> -1)
1686	script_reindex	  BEGIN
1687	script_reindex	    PRINT @objectname
1688	script_reindex	    SELECT @CMD = "DBCC DBREINDEX (" + @objectname
1689	script_reindex	             + "," + CHAR(39) + " " + CHAR(39)
1690	script_reindex	             + ", 90, SORTED_DATA_REORG)"
1691	script_reindex	    EXEC (@CMD)
1692	script_reindex	    FETCH NEXT FROM object_cursor INTO @objectname
1693	script_reindex	  END
1694	script_reindex	PRINT " "
1695	script_reindex	PRINT "All objects processed."
1696	script_reindex	CLOSE object_cursor
1697	script_reindex	DEALLOCATE object_cursor
1584	script_row_level_locks	DECLARE @rowid INT,
1585	script_row_level_locks	        @CMD VARCHAR(255)
1586	script_row_level_locks	SET NOCOUNT ON
1587	script_row_level_locks	SELECT @CMD = "PRINT " + CHAR(39) +
1588	script_row_level_locks	    "Enable row level locking on identified tables in " +
1589	script_row_level_locks	    DB_NAME() + CHAR(39)
1590	script_row_level_locks	PRINT @CMD
1591	script_row_level_locks	SELECT @rowid = (SELECT MIN(id) FROM sysobjects
1592	script_row_level_locks	                 WHERE userstat & 0xf = 1
1593	script_row_level_locks	                 AND sysstat & 0xf = 3)
1594	script_row_level_locks	WHILE @rowid IS NOT NULL
1595	script_row_level_locks	  BEGIN
1596	script_row_level_locks	    SELECT @CMD = "PRINT " + CHAR(39) +
1597	script_row_level_locks	                  OBJECT_NAME(@rowid) + CHAR(39)
1598	script_row_level_locks	    PRINT @CMD
1599	script_row_level_locks	    SELECT @CMD = "EXEC sp_tableoption " + CHAR(39) +
1600	script_row_level_locks	                  OBJECT_NAME(@rowid) + CHAR(39) + "," +
1601	script_row_level_locks	                  CHAR(39) + "insert row lock" + CHAR(39) +
1602	script_row_level_locks	                  "," + CHAR(39) + "true" + CHAR(39)
1603	script_row_level_locks	    PRINT @CMD
1604	script_row_level_locks	    SELECT @rowid = (SELECT MIN(id) FROM sysobjects
1605	script_row_level_locks	                     WHERE userstat & 0xf = 1
1606	script_row_level_locks	                     AND sysstat & 0xf = 3
1607	script_row_level_locks	                     AND id > @rowid)
1608	script_row_level_locks	  END
425	script_showcontig	DECLARE @id int
426	script_showcontig	SET NOCOUNT ON
427	script_showcontig	SELECT @id = MIN(id) FROM sysobjects
428	script_showcontig	WHERE id IN (SELECT id FROM sysindexes
429	script_showcontig	             WHERE rows > 0)
430	script_showcontig	AND type = "U"
431	script_showcontig	WHILE @id IS NOT NULL
432	script_showcontig	  BEGIN
433	script_showcontig	    DBCC SHOWCONTIG(@id)
434	script_showcontig	    SELECT @id = MIN(id) FROM sysobjects
435	script_showcontig	    WHERE id > @id
436	script_showcontig	    AND id IN (SELECT id FROM sysindexes
437	script_showcontig	               WHERE rows > 0)
438	script_showcontig	    AND type = "U"
439	script_showcontig	  END
1363	script_tasks	SET NOCOUNT ON
1364	script_tasks	PRINT "/*"
1365	script_tasks	PRINT " NOTES: Use single quotes only when setting up tasks."
1366	script_tasks	PRINT "        This utility will wrap all character data in"
1367	script_tasks	PRINT "        double quotes. If you automate a restore from"
1368	script_tasks	PRINT "        this script, be sure to drop existing tasks to"
1369	script_tasks	PRINT "        prevent duplicates."
1370	script_tasks	PRINT " "
1371	script_tasks	PRINT " sp_addtask name,"
1372	script_tasks	PRINT "     subsystem, server, username, databasename, "
1373	script_tasks	PRINT "     enabled, -- 0=disabled, 1=enable,"
1374	script_tasks	PRINT "     freqtype, freqinterval,"
1375	script_tasks	PRINT "     freqsubtype, freqsubinterval,"
1376	script_tasks	PRINT "     freqrelativeinterval, freqrecurrencefactor,"
1377	script_tasks	PRINT "     activestartdate, activeenddate,"
1378	script_tasks	PRINT "     activestarttimeofday, activeendtimeofday,"
1379	script_tasks	PRINT "     nextrundate, nextruntime, runpriority,"
1380	script_tasks	PRINT "     emailoperatorname, retryattempts, retrydelay,"
1381	script_tasks	PRINT "     loghistcompletionlevel, emailcompletionlevel,"
1382	script_tasks	PRINT "     command,"
1383	script_tasks	PRINT "     loghistcompletionlevel, emailcompletionlevel,"
1384	script_tasks	PRINT "     description"
1385	script_tasks	PRINT "*/"
1386	script_tasks	 
1387	script_tasks	SELECT  "msdb..sp_addtask ",
1388	script_tasks	          (CHAR(34) + RTRIM(name) + CHAR(34) + CHAR(44)) +
1389	script_tasks	        (CHAR(13) + CHAR(10)) + "  " +
1390	script_tasks	          (CHAR(34) + RTRIM(subsystem) + CHAR(34) +
1391	script_tasks	           ",@@SERVERNAME," +
1392	script_tasks	           CHAR(34) +
1393	script_tasks	           RTRIM((SELECT name FROM master..syslogins
1394	script_tasks	                 WHERE suid = ownerloginid)) +
1395	script_tasks	           CHAR(34) + CHAR(44) + CHAR(34) +
1396	script_tasks	           RTRIM(databasename) + CHAR(34) + CHAR(44)),
1397	script_tasks	        (CHAR(13) + CHAR(10)) + "  " +
1398	script_tasks	          (RTRIM(CONVERT(CHAR(1),enabled)) + CHAR(44)),
1399	script_tasks	        (CHAR(13) + CHAR(10)) + "  " +
1400	script_tasks	          (RTRIM(CONVERT(CHAR(3),freqtype)) + CHAR(44) +
1401	script_tasks	           RTRIM(CONVERT(CHAR(3),freqinterval)) + CHAR(44)),
1402	script_tasks	        (CHAR(13) + CHAR(10)) + "  " +
1403	script_tasks	          (RTRIM(CONVERT(CHAR(3),freqsubtype)) + CHAR(44) +
1404	script_tasks	           RTRIM(CONVERT(CHAR(3),freqsubinterval)) + CHAR(44)),
1405	script_tasks	        (CHAR(13) + CHAR(10)) + "  " +
1406	script_tasks	          (RTRIM(CONVERT(CHAR(3),freqrelativeinterval)) + CHAR(44) +
1407	script_tasks	           RTRIM(CONVERT(CHAR(3),freqrecurrencefactor)) + CHAR(44)),
1408	script_tasks	        (CHAR(13) + CHAR(10)) + "  " +
1409	script_tasks	          (RTRIM(CONVERT(CHAR(8),activestartdate)) + CHAR(44) +
1410	script_tasks	           RTRIM(CONVERT(CHAR(8),activeenddate)) + CHAR(44)),
1411	script_tasks	        (CHAR(13) + CHAR(10)) + "  " +
1412	script_tasks	          (RTRIM(CONVERT(CHAR(6),activestarttimeofday)) + CHAR(44) +
1413	script_tasks	           RTRIM(CONVERT(CHAR(6),activeendtimeofday)) + CHAR(44)),
1414	script_tasks	        (CHAR(13) + CHAR(10)) + "  " +
1415	script_tasks	          (RTRIM(CONVERT(CHAR(8),nextrundate)) + CHAR(44) +
1416	script_tasks	           RTRIM(CONVERT(CHAR(6),nextruntime)) + CHAR(44) +
1417	script_tasks	           RTRIM(CONVERT(CHAR(2),runpriority)) + CHAR(44)),
1418	script_tasks	        (CHAR(13) + CHAR(10)) + "  " +
1419	script_tasks	          (CHAR(34) +
1420	script_tasks	           RTRIM((SELECT name FROM msdb..sysoperators
1421	script_tasks	                  WHERE id = emailoperatorid)) +
1422	script_tasks	           CHAR(34) + CHAR(44) +
1423	script_tasks	           CONVERT(CHAR(1),retryattempts) + CHAR(44) +
1424	script_tasks	           CONVERT(CHAR(1),retrydelay) + CHAR(44)),
1425	script_tasks	        (CHAR(13) + CHAR(10)) + "  " +
1426	script_tasks	          (CHAR(34) + RTRIM(command) + CHAR(34) + CHAR(44)),
1427	script_tasks	        (CHAR(13) + CHAR(10)) + "  " +
1428	script_tasks	          (CONVERT(CHAR(1),loghistcompletionlevel) + CHAR(44) +
1429	script_tasks	           CONVERT(CHAR(1),emailcompletionlevel) + CHAR(44)),
1430	script_tasks	        (CHAR(13) + CHAR(10)) + "  " +
1431	script_tasks	          (CHAR(34) + RTRIM(description) + CHAR(34)),
1432	script_tasks	        (CHAR(13) + CHAR(10)) + "GO" +(CHAR(13) + CHAR(10))
1433	script_tasks	FROM msdb..systasks
320	script_update_statistics	SET NOCOUNT ON
321	script_update_statistics	DECLARE @table varchar(30)
322	script_update_statistics	DECLARE object_cursor CURSOR FOR
323	script_update_statistics	SELECT name FROM sysobjects
324	script_update_statistics	WHERE type = "U"
325	script_update_statistics	OPEN object_cursor
326	script_update_statistics	FETCH NEXT FROM object_cursor INTO @table
327	script_update_statistics	WHILE (@@fetch_status <> -1)
328	script_update_statistics	  BEGIN
329	script_update_statistics	    IF (@@fetch_status <> -2)
330	script_update_statistics	      BEGIN
331	script_update_statistics	        PRINT @table
332	script_update_statistics	        EXEC ("UPDATE STATISTICS " + @table)
333	script_update_statistics	      END
334	script_update_statistics	    FETCH NEXT FROM object_cursor INTO @table
335	script_update_statistics	  END
336	script_update_statistics	PRINT "Statistics have been updated for all tables."
337	script_update_statistics	CLOSE object_cursor
338	script_update_statistics	DEALLOCATE object_cursor
60	script_user_xps	/* build create scripts for existing user definded remote
61	script_user_xps	   stored procedures. Grant execute permission to all users
62	script_user_xps	   with sysprotects entries for the xp */
63	script_user_xps	 DECLARE @procedure varchar(255),
64	script_user_xps	         @objectName varchar(30),
65	script_user_xps	         @file varchar(255),
66	script_user_xps	         @user varchar(30)
67	script_user_xps	 SET NOCOUNT ON
68	script_user_xps	  DECLARE object_cursor CURSOR
69	script_user_xps	  FOR
70	script_user_xps	  SELECT DISTINCT o.name, c.text, u.name
71	script_user_xps	  FROM master..sysobjects o, master..syscomments c,
72	script_user_xps	       master..sysprotects p, master..sysusers u
73	script_user_xps	  WHERE c.id = o.id
74	script_user_xps	  AND p.id = o.id
75	script_user_xps	  AND p.uid = u.uid
76	script_user_xps	  AND o.type = "X"
77	script_user_xps	  AND o.category = 0
78	script_user_xps	 OPEN object_cursor
79	script_user_xps	   FETCH NEXT FROM object_cursor INTO @objectName, @file, @user
80	script_user_xps	     WHILE (@@fetch_status <> -1)
81	script_user_xps	       BEGIN
82	script_user_xps	         SELECT @procedure = "EXECUTE sp_addextendedproc " + CHAR(39) +
83	script_user_xps	                             @objectName + CHAR(39) + ", " + CHAR(39) +
84	script_user_xps	                             @file + CHAR(39)
85	script_user_xps	         PRINT @procedure
86	script_user_xps	         PRINT "GO"
87	script_user_xps	         SELECT @procedure = "GRANT EXECUTE  ON master.." +
88	script_user_xps	                             @objectName + " TO " + @user
89	script_user_xps	         PRINT @procedure
90	script_user_xps	         PRINT "GO"
91	script_user_xps	         PRINT ""
92	script_user_xps	     	 FETCH NEXT FROM object_cursor INTO @objectname, @file, @user
93	script_user_xps	      END
94	script_user_xps	 DEALLOCATE object_cursor
339	script_users	DECLARE @use VARCHAR(50),
340	script_users	        @line VARCHAR(255)
341	script_users	                                SET NOCOUNT ON
342	script_users	SELECT @use = "USE " +
343	script_users	              (SELECT name FROM master..sysdatabases
344	script_users		       WHERE dbid = (SELECT dbid
345	script_users	                             FROM master..sysprocesses
346	script_users		                     WHERE spid = @@SPID))
347	script_users	PRINT @use
348	script_users	PRINT "GO"
349	script_users	PRINT "/*============ sp_helpuser ============"
350	script_users	PRINT "Compare this to the sp_helpuser output"
351	script_users	PRINT "at the end of the results when this"
352	script_users	PRINT "script is run. They should be identical."
353	script_users	PRINT ""
354	script_users	EXEC sp_helpuser
355	script_users	PRINT "=======================================*/"
356	script_users	PRINT "/****** declare variables ******/"
357	script_users	PRINT "DECLARE @cmd VARCHAR(255),"
358	script_users	PRINT "        @lastsuid INT,"
359	script_users	PRINT "        @suid INT,"
360	script_users	PRINT "        @lastuid INT,"
361	script_users	PRINT "        @uid INT"
362	script_users	PRINT "SET NOCOUNT ON"
363	script_users	PRINT "/****** drop existing alias ******/"
364	script_users	PRINT "SELECT @lastsuid = 0"
365	script_users	PRINT "WHILE (SELECT MIN(suid) FROM sysalternates"
366	script_users	PRINT "       WHERE suid > @lastsuid) IS NOT NULL"
367	script_users	PRINT "  BEGIN"
368	script_users	PRINT "    SELECT @suid = (SELECT MIN(suid) FROM sysalternates"
369	script_users	PRINT "                    WHERE suid > @lastsuid)"
370	script_users	SELECT @line = "SELECT @cmd = " + CHAR(39) + "sp_dropalias" + CHAR(39) + " +"
371	script_users	PRINT @line
372	script_users	PRINT "                   (SELECT name FROM master..syslogins"
373	script_users	PRINT "                   WHERE suid = @suid)"
374	script_users	PRINT "    EXECUTE (@cmd)"
375	script_users	PRINT "    SELECT @lastsuid = @suid"
376	script_users	PRINT "  END"
377	script_users	PRINT "/****** drop existing users ******/"
378	script_users	PRINT "SELECT @lastuid = 0"
379	script_users	PRINT "WHILE (SELECT MIN(uid) FROM sysusers"
380	script_users	PRINT "       WHERE uid > @lastuid) IS NOT NULL"
381	script_users	PRINT "  BEGIN"
382	script_users	PRINT "    SELECT @uid = (SELECT MIN(uid) FROM sysusers"
383	script_users	PRINT "                   WHERE uid > @lastuid"
384	script_users	SELECT  @line = "       AND name <> " + CHAR(39) + "dbo" + CHAR(39) + ")"
385	script_users	PRINT @line
386	script_users	SELECT @line = "SELECT @cmd = " + CHAR(39) + "sp_dropuser " + CHAR(39) + " + "
387	script_users	PRINT @line
388	script_users	PRINT "                   (SELECT name FROM sysusers"
389	script_users	PRINT "                   WHERE uid = @uid)"
390	script_users	PRINT "    EXECUTE (@cmd)"
391	script_users	PRINT "    SELECT @lastuid = @uid"
392	script_users	PRINT "  END"
393	script_users	PRINT "/************** add users *******/"
394	script_users	SELECT "EXECUTE sp_adduser " + RTRIM(u.name) + ", " + RTRIM(l.name)
395	script_users	FROM sysusers u,
396	script_users	     master..syslogins l
397	script_users	WHERE l.suid = u.suid
398	script_users	AND u.name <> "dbo"
399	script_users	PRINT "/************** add alias *******/"
400	script_users	SELECT "EXECUTE sp_addalias " + RTRIM(l.name) + ", " + RTRIM(u.name)
401	script_users	FROM master..syslogins l,
402	script_users	     sysusers u,
403	script_users	     sysalternates a
404	script_users	WHERE u.suid = a.altsuid
405	script_users	AND l.suid = a.suid
406	script_users	AND a.suid IN (SELECT suid FROM master..syslogins)
407	script_users	PRINT "GO"
408	script_users	PRINT "sp_helpuser"
409	script_users	PRINT "GO"