October 2007 Blog Posts
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...
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.
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...
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...
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...