How to find some text in any object (table, stored procedure, view,UDF, and more) of SQL Server 2005/2008?

Don’t “waste” your time to write or use some custom code, SP etc. Just download this great, freeware tool xSQL Object Search and enjoy his fast work and advanced search options.

From authors webpage: http://www.xsqlsoftware.com/Product/Sql_Database_Object_Search.aspx

xSQL Object Search is a free SQL tool that provides for locating database objects by checking their names and/or definition against a search criteria. You can search on one or multiple databases simultaneously, select from various search options, search for all or specific objects types, export the results to output files and more. Supports SQL Server 2008 and SQL Server 2005.

Requirements

xSQL Object Search runs on Windows operating systems, client and server, that have the .NET framework 2.0 installed.

Features

Search on one or all databases – choose to find the objects that meet the search criteria on one database or on all databases in the selected SQL Server.

Support all types of database objects – from tables and views to CLR objects and Xml Schema Collections, you can search all database object types.

Search the name or the definition – choose to search on the name of the objects, the T-SQL statement definition of the objects or on both.

A range of search options

• Exact: Searches for objects whose name or definition is exactly the same as the search text.
• Starts With: Locates objects whose name or definition starts with the specified search text.
• Ends With: Locates objects whose name or definition ends with the specified search text.
• Contains: Locates objects whose name or definition contains the specified search text.
• SQL Server Expression: Locates objects based a SQL Server expression. Any SQL Server functions and operators that can be used in the "where clause" of a query can participate in the SQL Server Expression criteria.
• Regular Expression: Locates objects whose name or definition meets a regular expression pattern.

View object definition – view the T-SQL statement that defines the object with a click of a button.

Export search results – easily export the result of the search to a comma-separated-value file.

Simple and intuitive interface – a very simple interface allows you to finish your task with very few clicks. Asynchronous operations provides for a smooth search with status messages and counts.

Thank God it’s Friday :)

C#: SQL Server data types equivalents in .NET Framework

Below comparision table as image. If you find it useful then will be easy to save.

Transact-SQL: Show real size of tables in database on your harddisk

My WSUS database has 1.4 GB size… I was wondering what Microsoft did to achieve this kind of ‘goal’ ;) with just only about 1500 computers inside.  I would like to know which tables take the most space on my HD. That was tbXml with 1GB size… Amazing…

You can find helpful SQL code on Pinal Dave webpage:

http://blog.sqlauthority.com/2007/01/10/sql-server-query-to-find-number-rows-columns-bytesize-for-each-table-in-the-current-database-find-biggest-table-in-database/

WSUS: T-SQL – Move all computers from Unassigned Computers group into another

When recently almost 1500 new computers reported in our WSUS I thought, damn, must to move them from Unassigned Computers group into suitable groups (notebooks, desktops etc.) for easiest management. Unfortunatelly it is a big pain on ass (we don’t use client targeting option in our environment) because of:

- there is no drag & drop in WSUS console (!)

- if you select a lot of computers manually and choose from menu to move them into another group the WSUS Console MMC often start to freeze :/

So.. I look on this 1500 computers and was really in bad mood because of necessity to spend few hours on manually selection (by Ctrl+click) computers and choosing move command from context menu… H+O+R+R+I+B+L+E ;)

Fortunately we have access to WSUS database, so after short investigation and few tries I wrote SQL code which move all computers (selected by you through computer name pattern)  from Unassigned Computers group into group of your choice. After few minutes and few modifications (computer names pattern) the task is done.

I use it on WSUS 3.0 SP1 database. The code is selfexplained I hope. Enter your data in lines with ++++++ Change it to your needs ++++++ comment and run in WSUS database context.

SQL code below:

-- =============================================
-- Author:        Marek Sliwinski ( http://binaryelves.wordpress.com )
-- Create date: 2009-03-04
-- Description:    WSUS 3.0 SP1 
--                Move all computers with selected name (pattern)
--                from UNASSIGNED COMPUTERS group into selected by you
-- =============================================
DECLARE
     @targetGroupID uniqueidentifier
    ,@computerID    nvarchar(256)
    ,@counter        int

-- to show info on the end how many computers have been added
SET @counter = 0

/* Help view, show all WSUS Groups
SELECT
    *
FROM
    tbTargetGroup
*/
SELECT
    @targetGroupID = TargetGroupID
FROM
    tbTargetGroup
WHERE
    [Name] = 'NAME OF YOUR TARGET GROUP' -- ++++++ Change it to your needs ++++++

DECLARE unassigned_computers_list_cursor CURSOR FOR

SELECT
     ComputerID
    --,FullDomainName
    --,a.TargetID
FROM
    tbComputerTarget as a
    INNER JOIN tbTargetInTargetGroup as b
    ON a.TargetID = b.TargetID
WHERE
    FullDomainName LIKE '%computers pattern%'  -- computers pattern, ++++++ Change it to your needs ++++++
    AND TargetGroupID = 'B73CA6ED-5727-47F3-84DE-015E03F6A88A' -- this is Unassigned Computers group ID, don't change

OPEN unassigned_computers_list_cursor

FETCH NEXT FROM unassigned_computers_list_cursor INTO @computerID

WHILE @@FETCH_STATUS = 0
    BEGIN
        -- add computer to target group
        EXEC spAddComputerToTargetGroup @targetGroupID, @computerID

        SET @counter = @counter + 1

        FETCH NEXT FROM unassigned_computers_list_cursor INTO @computerID
    END

CLOSE unassigned_computers_list_cursor
DEALLOCATE unassigned_computers_list_cursor

print 'Number of computers moved to new group: ' + CAST(@counter as varchar)

Thank God it’s Friday :)

Computers for weekend ;)

C#: WinForms – How to change DataGridView cells color or other attributes

We can use for this _CellFormatting event. See below example:

private void DataGridView1_CellFormatting(object sender,
                        DataGridViewCellFormattingEventArgs e)
{
    DataGridView gv = (DataGridView)sender;

    if (gv.Columns[e.ColumnIndex].Name == "ColumnName")
    {
        // If cell has a value and contains char '-'
        if (e.Value != null && e.Value.ToString().Contains("-"))
        { // Change cell text color to red
            e.CellStyle.ForeColor = Color.Red;
            //e.CellStyle.Font = new Font("Arial", 10, FontStyle.Bold);
        }
    }
}

Thank God it’s Friday :)

Democracy is different in America. For example: women can vote but horse can not!

Regular Expressions: Remove all special characters from a string, allowing only alphanumeric and chars: ‘.’ and ‘-’

Remove all special characters from a string, allowing only alphanumeric and chars: ‘.’ and ‘-’

Example:

string source = ".ąśę0123^%($&Marek&*(@&@#-";
string result
  = System.Text.RegularExpressions.Regex.Replace(source, @"[^\w\.-]", "");
// result= ".ąśę0123Marek-"

Over 60 Free Controls from DevExpress

Great .NET components maker has published some time ago a package of free controls for WinForms and ASP.NET.

They have to be installed with entire DevExpresss Trial package so some times I didn’t remember which components are free. Unfortunately it is very hard to find the Free Components webpage from DevExpress.com site so I decide to post it here as a kind of personal shorcut ;) Enjoy everybody :)

http://www.devexpress.com/Products/Free/WebRegistration60/