Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links


This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog


Post Categories

About me

October 2007 Blog Posts

The undocumented sp_MSforeachtable procedure

As database administrators or developers, sometimes we need to perform an action on all of the tables within a database. For example, we may want to disable check constraints for all tables prior to loading a bunch of data; or perhaps we need to run DBCC CHECKTABLE for every table in our database to ensure the integrity of all pages and structures that make up the tables. We could, of course, create a script that uses a cursor to loop through all of the tables in the database to do our bidding through dynamic SQL. Yuck! Fortunately, there's a better way. It's the...

posted @ Tuesday, October 23, 2007 11:21 AM | Feedback (21) | Filed Under [ T-SQL SQL Server ]

DevLink Technical Conference presentation

Thanks to all of you who attended the DevLink Technical Conference in Nashville, Tennessee! It was great to see such a turnout for the event. Big kudos goes to all those who carried the load and championed this event. Putting on an event like this is no minor task and is definitely a labor of love. As promised in my session, here are the presentation materials I used - the slide deck and the demo scripts. I hope you found it worth your while. Cheers! Joe

posted @ Monday, October 15, 2007 8:19 AM | Feedback (1) | Filed Under [ Notification Services SQL Server Current Events ]

When was a SQL Server table or view last altered?

In a recent post, I demonstrated how the sys.procedures catalog view could be used to determine the date and time that a stored procedure was last modified in SQL Server 2005.  A similar technique can be used to determine the last time a user table was altered. The following query illustrates this technique. SELECT [name] ,create_date ,modify_dateFROM sys.tables The modify_date column is updated whenever a column is added or altered for the table. It's also updated if the clustered...

posted @ Thursday, October 11, 2007 7:58 AM | Feedback (18) | Filed Under [ T-SQL SQL Server ]

Enabling and disabling logins in SQL Server 2005

In SQL Server, much like other services with users, login accounts can be enabled or disabled. An enabled login can be authenticated and allowed access to database resources. A disabled login is not allowed to establish a connection to the SQL Server instance. For example, let's create a login called JoeUser. CREATE LOGIN JoeUser WITH PASSWORD = 'P@$$w0rd!' ,DEFAULT_DATABASE = AdventureWorks USE AdventureWorks;CREATE USER JoeUser In the first statement, the JoeUser login was created with a fairly complex password and a default database of AdventureWorks. Afterward, we switch to the AdventureWorks database and...

posted @ Wednesday, October 10, 2007 7:04 AM | Feedback (12) | Filed Under [ T-SQL SQL Server ]

How to tell when the SQL Server service was started

Ever wondered if it is possible to programmatically determine when the SQL Server service for an instance was last started?  It would be nice to have a built-in function that explicitly reports the time of the last service start, but one doesn't exist. But we can still estimate the time of the service start to within a few seconds, which should be good enough for anything less than the exact calculations required for most strict Service Level Agreement (SLA). Since we know that tempdb is recreated each time the SQL Server service starts, we can look to the creation date of that system database to...

posted @ Wednesday, October 03, 2007 6:21 AM | Feedback (13) | Filed Under [ T-SQL SQL Server ]

Powered by:
Powered By Subtext Powered By ASP.NET