CJayMead’s SQL Server Blog Skip to content

CJayMead's SQL Server Blog

Hi, my name is C-Jay, and welcome to my blog which covers SQL Server, .NET, and more..

SQLBits – The 7 Wonders of SQL

Sep 3
General

www.sqlbits.com

SQL Bits

SQLBits – The 7 Wonders of SQL conference, is taking place from September 30th to October 2nd at York University.

SQLBits is the largest SQL Server conference in Europe and the previous six conferences have established it as a must-attend event for DBAs, developers and BI professionals who work with Microsoft SQL Server in the UK and Europe. We are making this the biggest event ever with capacity for over 500 SQL Server professionals.

This time SQLBits will be running the three day format that was so successful in South Wales last year. Day one will be a training day, featuring in-depth full day seminars by leading SQL Server professionals; day two will be a deep-dive conference day with advanced sessions delivered by the best speakers from the SQL Server community; and day three will be the traditional SQLBits community conference day, with a wide range of sessions covered all aspects of SQL Server at all levels of ability.

There will be a charge to attend days one and two, but day three, the Saturday, will as usual be completely free to attend allowing those without any budget still to be able to attend and experience a great day of training.

The conference will be held at the Heslington Campus of York University, a modern venue easily accessible from all parts of the country by road and rail.

Leave a Comment , Read more

FREE Level 400 SQL Server Performance Monitoring & Tuning Webcasts

Sep 2
General

FREE Level 400 SQL Server Performance Monitoring & Tuning Webcasts at

http://www.sqlworkshops.com/webcast

This is a great resource for performance monitoring and tuning :)

Leave a Comment , , , Read more

List of tables and indexes, with associated File Group

Sep 2
Transact-SQL

The script below (written using SQL Server 2005), returns a list of all tables (along with the schema name), and indexes (where they exist), and the name of the associated file group.

This will useful to find out which objects have been assigned to the wrong file group.

I have a database with over 10 file groups, and wanted to find out why there was over 50Gb of data in the PRIMARY file group, even though there were only a couple of tables in that group. When I ran this script, I discovered there were some indexes that had been incorrectly created in the PRIMARY group!!!

SELECT  SCHEMA_NAME(T.Schema_ID) AS SchemaName,
        T.Name AS TableName,
        ISNULL(I.[name], 'N/A') AS IndexName,
        CASE    WHEN I.index_id = 0 THEN 'N/A'
                WHEN I.index_id = 1 THEN 'Clustered Index'
                WHEN I.index_id = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'
                ELSE 'Non Clustered Index'
        END AS IndexType,
        F.name AS IndexFileGroupName
FROM sys.tables T WITH (NOLOCK)
LEFT OUTER JOIN sys.indexes I
    ON T.object_id = I.object_id
LEFT OUTER JOIN sys.filegroups F
    ON I.data_space_id = F.data_space_id
WHERE T.type = 'U'
ORDER BY SCHEMA_NAME(T.Schema_ID), T.Name
Leave a Comment , , , , Read more

Search all table columns for specific text

Aug 20
Transact-SQL

I needed to find out which columns within all tables in the database, contained a specific string of text. I therefore wrote the script below to do exactly that :)

CREATE TABLE TempTableColumnResults
( TableName VARCHAR(100),
  ColumnName VARCHAR(100)
)

DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(200)
DECLARE @SQL VARCHAR(1000)
DECLARE @SearchText VARCHAR(100)

SET @SearchText = 'Text to find'

DECLARE TableColumns CURSOR FAST_FORWARD
FOR SELECT
        O.name AS TableName,
        C.name AS ColumnName
    FROM SYSCOLUMNS C
    INNER JOIN SYSOBJECTS O
    ON C.ID = O.ID
    WHERE O.xtype IN ('U')
    AND C.xtype NOT IN (34,241,98) --Exclude image, sql_variant, xml data types
    ORDER BY O.name, C.ColOrder

OPEN TableColumns

FETCH NEXT FROM TableColumns
    INTO @TableName, @ColumnName

SET NOCOUNT ON

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'INSERT INTO TempTableColumnResults (TableName, ColumnName) '
    SET @SQL = @SQL + 'SELECT ' + CHAR(39) + @TableName + CHAR(39) + ', '
    SET @SQL = @SQL + CHAR(39) + @ColumnName + CHAR(39) + ' '
    SET @SQL = @SQL + 'FROM ' + QUOTENAME(@TableName) + ' '
    SET @SQL = @SQL + 'WHERE ' + QUOTENAME(@ColumnName) + ' LIKE '
    SET @SQL = @SQL + CHAR(39) + '%' + @SearchText + '%' + CHAR(39) + ' '
    EXEC (@SQL)
    FETCH NEXT FROM TableColumns
        INTO @TableName, @ColumnName
END

CLOSE TableColumns
DEALLOCATE TableColumns

SELECT DISTINCT TableName, ColumnName FROM TempTableColumnResults

DROP TABLE TempTableColumnResults
Leave a Comment , , , , Read more

Transfer Logins and Passwords to a new server

Aug 11
Installation & Configuration, Transact-SQL

If you need to transfer the logins and passwords from one server or instance to another server or instance, please refer to this very useful article from Microsoft:

How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

Leave a Comment , , , , , , Read more
CJayMead's SQL Server Blog

  • Login



  • Forum Latest Activity

  • Upcoming events

    • SQLBits - The 7 Wonders of SQL - Training Day more » on 30 Sep 2010 09:00
      in 26 days and 06:57 hours.
    • SQLBits - The 7 Wonders of SQL - Deep Dive Conference Day more » on 01 Oct 2010 09:00
      in 27 days and 06:57 hours.
    • SQLBits - The 7 Wonders of SQL - Community Conference Day more » on 02 Oct 2010 09:00
      in 28 days and 06:57 hours.

  • Pages

  • Twitter: CJayMead

  • Most Viewed Posts/Pages

  • RSS SQL Server Team Blog

  • Tags

  • Categories

Copyright © 2010 CJayMead's SQL Server Blog Design by SRS Solutions top