BPSoftware.com Blog

Tuesday, November 25, 2008

SQL: DBCC CHECKTABLE on multiple tables

Part of decent SQL database maintenence is periodically checking the integrity, both logical and physical, of database objects. SQL Server has the DBCC CHECKDB command that will check the integrity of the objects and report back the results. DBCC CHECKDB runs CHECKALLOC, CHECKTABLE and CHECKCATALOG on the specified database.

in SQL 2005 you can use table variables if you do not need or want to store the data you can store it in a table variable instead of a temporary table

in SQL 2005 you can use table variables if you do not need or want to store the data you can store it in a table variable instead of a temporary table

DBCC CHECKTABLE allows you to check the integrity of a specified table or indexed view. If there are a large number of tables to check, the task of running the command for each tablename could be time consuming. Creating a script that loops through a set of database tables and runs DBCC CHECKTABLE for each, is a bit easier.

The sys.objects contains each of the user-defined schema objects in a database. Rows that are of [Type] = 'U' are tables. This makes it easy to get all the tables within a database (of course other filters can be applied). The following script will loop through and run the DBCC CHECKTABLE command for each table in the database (the command can be easily replaced to perform another action on each returned table):

-- set options
3

SET NOCOUNT ON;
4

SET ANSI_PADDING ON;
5

SET ANSI_NULLS ON;
6

SET QUOTED_IDENTIFIER ON;
7

-- declare table to store table names
10

-- in SQL 2005 you can use table variables
11

-- if you do not need or want to store the data you can store it in a table
12

-- variable instead of a temporary table
13

DECLARE @tablenames TABLE (
14

TableName VARCHAR(255)
15

);
16

-- populate table with tablenames
20

INSERT INTO @tablenames
21

SELECT
22

name
23

FROM
24

sys.objects
25

-- additional filters can be set to narrow table selection
26

WHERE
27

([type] = 'U');
28

-- Declare a cursor to loop through table
32

DECLARE tablenames INSENSITIVE SCROLL CURSOR
33

FOR
34

SELECT
35

TableName
36

FROM
37

@tablenames;
38

-- Open the cursor.
43

OPEN tablenames
44

-- declare variables
48

DECLARE @curtablename AS VARCHAR(255);
49

DECLARE @cmd AS VARCHAR(1024);
50

-- Loop through all the tables
54

WHILE (1=1)
55

BEGIN
56

FETCH NEXT FROM tablenames INTO @curtablename;
57

IF @@fetch_status <> 0 BREAK;
58

-- run the command
59

set @cmd = 'DBCC CHECKTABLE ([' + rtrim(@curtablename) + '])';
60

exec (@cmd)
61

print @cmd + CHAR(10) + CHAR(13);
62

end
63

-- Close cursor
67

CLOSE tablenames;
68

DEALLOCATE tablenames;
69

Labels: Code, SQL

posted by Brad Prendergast at 9:04:00 PM (0 comments)
Links to this post
Permalink

Monday, November 24, 2008

SQL: Index Fragmentation Maintenance

In a previous post, I discussed SQL Server's built in ability for suggesting missing indexes. As records are inserted, updated and deleted from tables indexes can become fragmented.

A fragmented index affects performance (table scans and index seeks) when the DBMS needs to search for records.

This probably leaves the question, "How can one defragment the indexes of SQL tables?"

Within SQL Server 2005 and greater you can setup a maintenance plan to defragment all the indexes within all or a particular database. You can also right click on an index and select to either Rebuild or Reorganize and index (rebuilding will re-create the index whereas reorganize reorders the index pages).

I prefer to have a little more control and use the following script as part of my maintenance plan. This script allows for a little more control in the selecting of tables, indexes and databases to maintain. I tend to rebuild indexes that are more than 30% fragmented and reorganize those that are greater than 5%.

SQL Server 2005 (and greater) has index information more readily accessible. The key to this script is the index information that is contained in sys.dm_db_index_physical_stats. This table returns the size and fragmentation information for each index.

/** **/
2

-- Create table to hold index information
3

SET NOCOUNT ON;
4

create table _IndexInformation (
6

database_id smallint,
7

ObjectName varchar(255),
8

[object_id] int,
9

IndexName varchar(255),
10

[index_id] int,
11

index_level int,
12

index_type_desc varchar(60),
13

avg_fragmentation_in_percent float,
14

fragment_count float,
15

page_count bigint,
16

record_count bigint,
17

index_depth int
18

);
19

/** **/

/** **/
2

-- clear table contents
3

truncate table _IndexInformation;
4

-- populate table
6

insert into _IndexInformation
7

select
8

database_id,
9

object_name(i.object_id) AS [ObjectName],
10

i.[object_id],
11

i.name AS [IndexName],
12

dm.index_id,
13

dm.index_level,
14

dm.index_type_desc,
15

dm.avg_fragmentation_in_percent,
16

dm.fragment_count,
17

dm.page_count,
18

dm.record_count,
19

dm.index_depth
20

from
21

-- database, table and index information can be specified
22

/*
23

sys.dm_db_index_physical_stats (
24

{ database_id NULL 0 DEFAULT }
25

, { object_id NULL 0 DEFAULT }
26

, { index_id NULL 0 -1 DEFAULT }
27

, { partition_number NULL 0 DEFAULT }
28

, { mode NULL DEFAULT }
29

)
29

This example uses database 1; you should replace this with the ID of the database you wish to process
30

*/
31

sys.dm_db_index_physical_stats(1,null,null,null,'detailed') dm
32

INNER JOIN sys.indexes i ON
33

(i.object_id = dm.object_id) AND (i.index_id = dm.index_id);
34

/** **/
35

/** **/
2

-- read index table and maintain indexes
3

SET NOCOUNT ON;
4

SET ANSI_PADDING ON;
5

SET ANSI_NULLS ON;
6

SET QUOTED_IDENTIFIER ON;
7

-- Declare variables.
10

declare @tablename as varchar(255)
11

declare @indexname as varchar(255)
12

declare @frag as float
13

declare @cmd as varchar(1024)
14

-- Declare a cursor.
16

declare indexes insensitive scroll cursor
17

for
18

select
19

ObjectName,
20

IndexName,
21

avg_fragmentation_in_percent
22

from
23

_IndexInformation
24

where
25

(avg_fragmentation_in_percent >= 5);
26

-- Open the cursor.
28

open indexes
29

-- Loop through all the indexes.
32

while (1=1)
33

begin
34

fetch next from indexes into @tablename, @indexname, @frag;
35

if @@fetch_status <> 0 break;
36

-- if fragmentation is above 30% rebuild index
37

-- if fragmentation is above 5% reorganize index
38

if (@frag >= 30)
39

begin
40

set @cmd = 'ALTER INDEX [' + rtrim(@indexname) + '] ON [' + @tablename +'] REBUILD;'
41

exec (@cmd)
42

print @cmd;
43

end
44

else
45

if (@frag >= 5)
46

begin
47

set @cmd = 'ALTER INDEX [' + rtrim(@indexname) + '] ON [' + @tablename +'] REORGANIZE;'
48

exec (@cmd)
49

print @cmd;
50

end;
51

end
52

-- Close cursor
56

close indexes;
57

deallocate indexes;
58

/** **/
59

/** **/
2

-- delete index table
3

drop table _IndexInformation;
4

/** **/
5

I have the script schedule to as part of my database maintenance plan. I run the script several times with additional selection criteria limiting it to groups of tables in a particular database.

Labels: Code, SQL

posted by Brad Prendergast at 9:51:00 PM (2 comments)
Links to this post
Permalink

Friday, November 21, 2008

Off-topic: Uhm, Rickroll?

Last weekend I did a search for a video on YouTube. When the search results came up I clicked the link to a video that seemed to be the most relevant. The video queued up and the video for Rick Astley's "Never Gonna Give You Up" song started to play. Over the video the words 'You have just been Rickrolled' appeared. It took me a moment, but then I realized something funny was going on. I did a quick Google search for 'Rickrolled' and found out exactly what it was...

The weird thing is that I had never heard any hype or talk about 'Rickrolling' until last weekend. Over the past week, I have heard mention of Rickrolling in some form of media. 'Rickrolling' appeared in blogs, news articles and I even heard radio hosts discussing it.

The whole thing is sort of comical to me. Was it that I had never paid attention or is it that the RickRolling buzz is still new?

How long will it last?

Next we'll have "Vanilla Iced" or "Milli-Vanilli'd"

Now that gives me an idea.......

Labels: Misc

posted by Brad Prendergast at 7:25:00 AM (3 comments)
Links to this post
Permalink

Thursday, November 20, 2008

SQL: Where are the database files?

From within the SQL Server Management Studio you can right click on a database and view its properties to get a list of the physical files that make up a database. If you do not have access to SQL Management Studio and the database is online you can run the sp_helpfile stored procedure. The sp_helpfile stored procedure lists the files for the current (individual) database; the database must be online. What about retrieving file information for offline databases?
Another alternative for retrieving the list of database files for a single or collective set of databases is to query the sys.master_files table. You can query the sys.master_files database if the database is online, offline or in one of the other states. The following query retrieves database file information:

SELECT
2

database_id,
3

DB_Name(database_id) as database_name,
4

[file_id],
5

type_desc,
6

[name],
7

physical_name,
8

state_desc,
9

[size],
10

max_size
11

FROM
12

sys.master_files
13

WHERE
14

-- you can use the DB_ID function to return the
15

-- database id of a database by name
16

(database_id = DB_ID(N'master'))
17

-- if you know the id you can filter by it directly
18

-- (database_id = 1)
19

ORDER BY
20

type_desc DESC,
21

physical_name;

The sys.databases table has information about the database in the current SQL Server instance.

SELECT
2

database_id,
3

[name],
4

compatibility_level,
5

collation_name,
6

state_desc
7

FROM
8

sys.databases;

Labels: Code, SQL

posted by Brad Prendergast at 9:08:00 PM (0 comments)
Links to this post
Permalink

Tuesday, November 18, 2008

Show Desktop in my QuickLaunch Toolbar?

It was there before; now it's not.

Where is it?

How do I get it back?

Where is the Show Desktop shortcut on my Quick Launch toolbar?

The Quick Launch toolbar is a great place to place shortcuts to frequently used applications. One of my must have shortcuts, is the Show Desktop shortcut.

This shortcut quickly minimizes all open applications leaving a clear view of the desktop. The Show Desktop shortcut is generally available by default in the Quick Launch toolbar, however, what if it gets removed or it is no longer there?

Adding the Show Desktop shortcut back is easier than you think.

1. Open Notepad and enter the following text:

[Shell]
Command=2
IconFile=explorer.exe,3
[Taskbar]
Command=ToggleDesktop

2. Save the file in the Microsoft\Internet Explorer\Quick Launch folder that is found in your profile's Application Data directory.

3. Save the file as: Show Desktop.scf

Now it is back again!!

Labels: How-To, Tech

posted by Brad Prendergast at 8:41:00 PM (5 comments)
Links to this post
Permalink

Sunday, November 16, 2008

Command Line: Visual Source Safe

I have long used Microsoft® Visual SourceSafe® (VSS) for source code management and control. There are many ways to manage and control code through VSS. Visual SourceSafe® integrates nicely with Visual Studio, allowing for source control from directly within the integrated development environment (IDE). VSS also has its own front end application, which can be used as a stand alone application. There is also a command line application (ss.exe) that can be used to manage a SourceSafe database. If neither of the fore mentioned options are suitable, there is also an API that a developer can access to write their own source control front end to VSS.

The frontend application and Visual Studio integration I have generally found to be sufficient to manage a SourceSafe database, however there are a few tasks that I have found work best managed through the command line.

In order to used ss.exe you first must set the source safe directory (the SSDIR environment variable). When setting the SSDIR you must specify the path to the SourceSafe database in 8.3 format. For example:

Set the SourceSafe directory
set SSDIR=C:\DOCUME~1\BRAD\MYDOCU~1\VISUAL~2\BDS
set SSDIR=C:\PROGRA~1\MI53B4~1\SOURCE~1

Once you have the SSDIR set, you may need to work within a specific project.

Get a list of projects
"C:\Program Files\Microsoft Visual SourceSafe\ss.exe" dir
Set the current project
"C:\Program Files\Microsoft Visual SourceSafe\ss.exe" CP $/

Some useful tasks that are not easily acieved without the use od ss.exe:

Get a list of files to be purged
"C:\Program Files\Microsoft Visual SourceSafe\ss.exe" dir -d -r
Purge all files recursively
"C:\Program Files\Microsoft Visual SourceSafe\ss.exe" purge * -r
Move a project from one location to another
"C:\Program Files\Microsoft Visual SourceSafe\ss.exe" move $QualitySales $TZI
"C:\Program Files\Microsoft Visual SourceSafe\ss.exe" move $/VB.NET/TZI/qualitysales VB.NET/QualitySales

Labels: How-To, Tech

posted by Brad Prendergast at 4:22:00 PM (0 comments)
Links to this post
Permalink

Wednesday, November 12, 2008

SQL: Remove / Delete Orphan Users

As discussed in the post SQL Delete/Drop a User from each Database, when you delete a Login from SQL Server it does not remove them from the databases they have explicit permissions on. If a user login is removed, and they are not removed from the database you would have orphan users with permission to the database. The stored procedure listed will remove (delete) orphan datatabase user logins.

1 USE [master]
2 GO
3 SET ANSI_NULLS ON
4 GO
5 SET QUOTED_IDENTIFIER ON
6 GO
7
8 create procedure [dbo].[sp_DeleteOrphanUsers]
9 AS
10
11 -- revoke orphan user
12 declare @cmd as varchar(1000)
13 declare @uname as sysname
14
15 DECLARE my_cur INSENSITIVE SCROLL CURSOR
16 FOR
17 -- orphan user
18 select u.name from master..syslogins l right join
19 sysusers u on l.sid = u.sid
20 where l.sid is null and issqlrole <> 1 and isapprole <> 1
21 and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' and u.name <> 'sys'
22 and u.name <> 'system_function_schema')
23 order by u.name
24
25 open my_cur
26
27 WHILE (1 = 1)
28 begin
29 FETCH FROM my_cur INTO @uname
30 IF @@fetch_status <> 0
31 BREAK
32 set @cmd = 'exec sp_revokedbaccess ''' + rtrim(@uname) + ''''
33 print @cmd
34 exec (@cmd)
35
36 end
37 DEALLOCATE my_cur
38
39 return(0)

The procedure would be executed like the following:

1 use [databasename] /* where databasename is the name of the database to run the procedure on */
2 exec sp_DeleteOrphanusers

Labels: Code, How-To, SQL

posted by Brad Prendergast at 6:04:00 AM (2 comments)
Links to this post
Permalink

Tuesday, November 11, 2008

SQL Delete/Drop a User from each Database

Deleting a user from SQL server deletes them from the security system, however it does not remove them from databases they have 'securables' (permissions speficified) for. The following procedure will remove a user from each database and remove their account.

1 USE [master]
2 GO
3 SET ANSI_NULLS ON
4 GO
5 SET QUOTED_IDENTIFIER ON
6 GO
7 CREATE procedure [dbo].[sp_DeleteUser]
8 @username sysname = NULL
9 AS
10
11 -- declare @nam as sysname
12 declare @cmd1 as varchar(500)
13
14 if @username is not null
15 begin
16 set @cmd1 = 'PRINT ''?'' USE ? DROP USER ' + @username
17 exec sp_msforeachdb @command1= @cmd1
18 exec ('DROP LOGIN ' + @username)
19 return(0)
20 END
21 return (1)

The stored procedure is called like;