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;

1 exec sp_DeleteUser 'loginname' /* where loginname is the name of the account that you would like deleted */

Labels: Code, How-To, SQL

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

Is there an 'I' in phone?

I spend a lot of time reading various news articles to keep up with what is going on in the world of technology. I can't remember the last day that had passed without me reading something about the IPhone. Ironically, a few months ago I was due for a phone and ended up with grabbing an IPhone. Today, after reading the articles 'iPhones rule in US smart phone market' and 'The iPhone Is Now the Best Selling Phone In the U.S.' I started thinking about my own experience with the IPhone.

Overall, I think the IPhone is a pretty slick device, however I tend to lean towards it being a lot more hype than what is really there (I too had succumb to the hype). The phone seems to be more trendy and more of a statement than it is umph. I really enjoy the WiFi and Web browsing features along with the ease of use. The web on the 3G network has saved me a number of times while I have been out and about. The GPS and map application is a nice useful feature. There are also a lot of neat applications available through the App Store (some of the Apps are practical while others are more of a novelty that are a great 'ice breaker' for showing off your IPhone. The responsiveness of the touch screen and the motion of the IPhone is really neat! (You should check out Google Earth for the IPhone). The IPod software also allows for the need to carry only one device for phone and music.

Just as there are things that I like about the IPhone, there are several things that drive me absolutely crazy. The weird thing is that what drives me crazy seems to be trivial features to most phones available today. The lacking feature set is so mind numbing that they almost have me regretting ever picking up an IPhone.

Here is my list of the top 5 lacking features. These features make me want to literally pull my hair out. I have other minor gripes that I can live with, however these are the 'regret getting the phone' complaints:

1. NO Cut, Copy, Paste - for the life of me I do not understand why you can not use basic editing features while composing email, text messages or any other text functions (there isn't even a Select All).

2. NO MMS - Camera, IPod, YouTube, and many other multimedia rich options/applications and for the life of me I don't understand why you can not send or receive MMS messages with the IPhone.

3. NO Text forwarding or Resending - Yes, if you need to resend or forward a text message you need to retype it. I do mean retype it because as I had already mentioned there is not a cut, copy or paste function.

4. NO Camera Zoom - If you're going to pack all of this into a phone, why not at least add a camera zoom? If you're taking a picture with a phone, don't you think you'd want to be a bit closer?

5. NO Battery Life - The battery is draining, literally. I need to charge this thing once or twice a day (and I am not living on the phone like others; I consider my usage 'average').

Well, those are my big gripes for now, you should also check out PleaseFixTheiPhone.

Labels: Misc

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

Monday, November 10, 2008

SQL Optimization: Am I missing any indexes? - Part I

Lately, I have been doing a lot with SQL (2005) optimization. Database design plays an important role in the efficiency of a database. A key part of the design is the use of indexes. Indexes significantly affect the performance of a database whether it is during a SELECT, INSERT, UPDATE and DELETE Transact-SQL (DML) statement. The biggest gain from an index is when a user SELECTs data from a database. The designer of the database and application should have done their best to come up with indexes that accommodate how the database will be used. How can one be sure they’re not missing any indexes?

SQL Server 2005 has a feature that keeps track of Missing Index Information. This information is contained in the sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_details tables. The missing index information in these tables can be used to determine indexes that would improve database performance based upon its usage. The following query returns the indexes that would improve database performance. Before creating the indexes review the usage values to determine if the creation of the index is really necessary.

1 /* missing indexes*/
2 use [master]
3 SELECT IG.INDEX_GROUP_HANDLE,
4 ID.INDEX_HANDLE,
5 ID.DATABASE_ID,
6 DB_NAME(ID.DATABASE_ID) AS DATABASE_NAME,
7 ID.[OBJECT_ID],
8 OBJECT_NAME(ID.[OBJECT_ID],ID.DATABASE_ID) as TABLE_NAME,
9 ID.STATEMENT AS FULL_TABLE_NAME,
10 ID.EQUALITY_COLUMNS,
11 ID.INEQUALITY_COLUMNS,
12 ID.INCLUDED_COLUMNS,
13 GS.USER_SEEKS,
14 GS.USER_SCANS,
15 GS.LAST_USER_SEEK,
16 GS.LAST_USER_SCAN,
17 GS.AVG_TOTAL_USER_COST,
18 GS.AVG_USER_IMPACT,
19 GS.SYSTEM_SEEKS,
20 GS.SYSTEM_SCANS,
21 GS.LAST_SYSTEM_SEEK,
22 GS.LAST_SYSTEM_SCAN,
23 GS.AVG_TOTAL_SYSTEM_COST
24 FROM SYS.DM_DB_MISSING_INDEX_DETAILS AS ID
25 INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUPS AS IG
26 ON ID.INDEX_HANDLE = IG.INDEX_HANDLE
27 INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS AS GS
28 ON GS.GROUP_HANDLE = IG.INDEX_GROUP_HANDLE
29 ORDER BY ((GS.AVG_TOTAL_USER_COST * GS.AVG_USER_IMPACT) * (GS.USER_SEEKS + GS.USER_SCANS)) DESC
30
31

Using the above query result you could create the necessary index (the field names are listed where their values should be used):

1 CREATE NONCLUSTERED INDEX <unique index name>
2 ON <FULL_TABLE_NAME> (<EQUALITY_COLUMNS>,<INEQUALITY_COLUMNS>) -- exclude INEQUALITY_COLUMNS if NULL
3 INCLUDE (<INCLUDED_COLUMNS>); -- exclude INCLUDED_COLUMNS if NULL

The information in these tables is dynamic and When SQL Server is restarted, all of the missing index information is dropped from them. The index information is collected over time; therefore it is better to query this information when the server has been ‘used for a period of time’.

Labels: Code, How-To, SQL

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

Sunday, November 09, 2008

Meaningful Signature File Quotes

There are many entertaining and powerful quotes in circulation. I often find in correspondence that individuals include such quotes as part of their signature file (some applications allow for the rotation of quotes). Here is a list of a few of my favorite quotes, along with the name of who is responsible for the words of wisdom. I am interested in any other moving quotes as well (with the author's name as well):

"The only difference between me and a madman is that I'm not mad." -- Salvador Dali (1904 - 1989)

"There's a fine line between genius and insanity. I have erased this line." -- Oscar Levant (1906 - 1972)

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." -- Albert Einstein (1879 - 1955)

"Associate yourself with men of good quality if you esteem your own reputation. It is better be alone than in bad company." -- George Washington (1732-1799)

"Anyone who has never made a mistake has never tried anything new." -- Albert Einstein (1879 - 1955)

"He was so learned that he could name a horse in nine languages; so ignorant that he bought a cow to ride on." -- Benjamin Franklin (1706-1790)

"Don't find a fault. Find a remedy." -- Henry Ford (1863-1947)

"Have no fear of perfection - you'll never reach it." -- Salvador Dali (1904 - 1989)

"Many go fishing all their lives without knowing that it is not fish they are after." -- Henry David Thoreau (1817-1862)

"The quality of an individual is reflected in the standards they set for themselves." -- Ray Kroc (1902-1984)

"Blessed is he that expects nothing, for he shall never be disappointed." -- Benjamin Franklin (1706-1790)

"It has long since come to my attention that people of accomplishment rarely sat back and let things happen to them. They went out and happened to things." -- Leonardo da Vinci (1452 – 1519)

"The function of muscle is to pull and not to push, except in the case of the genitals and the tongue." -- Leonardo da Vinci (1452 – 1519)

"Few people are capable of expressing with equanimity opinions which differ from the prejudices of their social environment. Most people are even incapable of forming such opinions." -- Albert Einstein (1879 - 1955)

Labels: Misc

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