24. January 2008 by Bennett.

A new version of the SQL Server 2005 Best Practices Analyzer (BPA) is here.

On a related note, the BPA reported several operating system issues that I was unaware of, including an issue where SQL Server’s working set gets paged to disk during large file copies. More info and a link to the Technet article discussing this issue can be found here.

Posted in Windows OS, SQL Server | Print | No Comments »

22. January 2008 by Bennett.

Compatible with sql server 2k5:

USE master
CREATE TABLE #tempresults
(


[name] sysname,
db_size NVARCHAR(13),
[owner] sysname,
[dbid] smallint,
created NVARCHAR(11),
[status] NVARCHAR(600),
compatibility_level tinyint

)
INSERT INTO #tempresults
EXEC sp_helpdb
SELECT db.name, tr.db_size, db.compatibility_level,is_auto_shrink_on, state_desc, recovery_model_desc, page_verify_option_desc

FROM sys.databases db
JOIN #tempresults tr
ON db.name =tr.name

WHERE dbid >4 –This clause will exclude system DBs,master, model, msdb, and tempdb
ORDER BY db.name
DROP TABLE #tempresults

Posted in T-SQL, SQL Server | Print | No Comments »

14. January 2008 by Bennett.

I found an easy way to render code snippets in HTML. It is called the Simple Talk Code Prettifier. In addition to rendering, it also converts SQL keywords to uppercase and gives you proper indentation.  Also supports VB.  http://www.simple-talk.com/prettifier/default.php

By the way, the Simple Talk blog http://www.simple-talk.com is pretty cool.  My puny effort here pales by comparison.

Posted in SQL Server | Print | No Comments »

9. January 2008 by Bennett.

Here are a couple of different ways to search for a column name within a SQL Server database:

– using information_schema views (preferred method)

SELECT sc.table_name
   FROM information_schema.columns sc
   INNER JOIN information_schema.tables st
     ON sc.table_name = st.table_name
   WHERE st.table_type = ‘base table’
   AND sc.column_name = ‘ColumnToFind’
   ORDER BY sc.table_name

– old school
SELECT name
   FROM sysobjects
   WHERE id IN
   (
      SELECT id
        FROM syscolumns
        WHERE name = ‘ColumnToFind’ )
        AND xtype = ‘U’
        ORDER BY name

Posted in T-SQL, SQL Server | Print | No Comments »

8. January 2008 by Bennett.

Script to change SQL Server database modes via systables. This lets you set modes, such as EMERGENCY, that cannot be set via the sp_dboption stored procedure.

– change database mode via systables
USE Master
GO
– Determine the original database status
SELECT [Name], DBID, Status
  FROM master.dbo.sysdatabases
GO
– Enable system changes
sp_configure ‘allow updates’,1
GO
RECONFIGURE WITH OVERRIDE
GO
– Update the database status
UPDATE master.dbo.sysdatabases
   SET Status = 16
     WHERE [Name] = ‘DatabaseNameGoesHere’
GO
– Disable system changes
sp_configure ‘allow updates’,0
GO
RECONFIGURE WITH OVERRIDE
GO
– Determine the final database status
SELECT [Name], DBID, Status
   FROM master.dbo.sysdatabases
GO

/*Modes

1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.

Note that these are bit values. For example, 24 = 8+16, so 24 means that truncate log on checkpoint AND torn page detection
are both set.
*/

Posted in T-SQL, SQL Server | Print | No Comments »