Useful snippets for Microsoft SQL Server / T-SQL

In this post i will collect code snippets useful for my daily work with Microsoft SQL server. Maybe it is useful for you too.

Remove leading zeros in string

With this code snippet you can remove leading zeros from a varchar value. Very useful for account numbers or item numbers.

DECLARE @string varchar(50) = '000001000'

SELECT SUBSTRING(@string, PATINDEX('%[^0]%', @string+'.'), LEN(@string))

Get all tables in database with rowcount

With this query you can get all tables in a database with the number of rows.If you want to limit the tables to a spefic naming shema, just edit line 24 to your needs.

...
t.NAME NOT LIKE 'dt%'
...

To filter only tables with a specific row count you should modify line 27.

...
AND p.rows > 0 AND p.rows < 5
...

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
    ,t.object_id as t_OBJECT_ID
    , 'SELECT '''+t.name+''', * FROM [' + t.name +']' as Select_Statement
FROM
    sys.tables t
INNER JOIN     
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    AND p.rows > 0 AND p.rows < 5
GROUP BY
    t.Name, s.Name, p.Rows,t.object_id
ORDER BY
    p.[rows] DESC

Search for a specific column name in all tables (with row count)

You extend the statement to get all tables with row count to search for a column name. The query to get all tables with row count is put in a Common Table Expression (CTE) an can joined with the query to get all tables with specific column.

WITH tables_rows
AS
(
    SELECT 
        t.NAME AS TableName,
        s.Name AS SchemaName,
        p.rows AS RowCounts,
        SUM(a.total_pages) * 8 AS TotalSpaceKB, 
        CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
        SUM(a.used_pages) * 8 AS UsedSpaceKB, 
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
        CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
        ,t.object_id as t_OBJECT_ID
        , 'SELECT '''+t.name+''', * FROM ' + t.name as Select_Statement
    FROM 
        sys.tables t
    INNER JOIN      
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN 
        sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN 
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE 
        t.NAME NOT LIKE 'dt%' 
        AND t.is_ms_shipped = 0
        AND i.OBJECT_ID > 255
        --AND p.rows > 0 AND p.rows < 5
    GROUP BY 
        t.Name, s.Name, p.Rows,t.object_id
)

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
,trows.RowCounts
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
JOIN tables_rows as trows ON t.OBJECT_ID = trows.t_OBJECT_ID
WHERE c.name LIKE '%LI_No%'
ORDER BY schema_name, table_name;

Search for a value in whole database

-- Search all columns in all tables in a database for a string.
-- Does not search: image, sql_variant or user-defined types.
-- Exact search always for money and smallmoney; no wildcards for matching these.
--------------------------------------------------------------------------------
declare @SearchTerm nvarchar(4000) -- Can be max for SQL2005+
declare @ColumnName sysname
declare @TableName sysname

--------------------------------------------------------------------------------
-- SET THESE!
--------------------------------------------------------------------------------
set @SearchTerm = N'4300321' -- Term to be searched for, wildcards okay
set @ColumnName = N'Source%' -- Use to restrict the search to certain columns, wildcards okay, null or empty string for all cols
set @TableName = N'Elsdorfer$%' -- Use to restrict the search to certain columns, wildcards okay, null or empty string for all cols
--------------------------------------------------------------------------------
-- END SET
--------------------------------------------------------------------------------

set nocount on

declare @TabCols table (
      id int not null primary key identity
    , table_schema sysname not null
    , table_name sysname not null
    , column_name sysname not null
    , data_type sysname not null
)
insert into @TabCols (table_schema, table_name, column_name, data_type)
    select t.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
    from INFORMATION_SCHEMA.TABLES t
        join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_SCHEMA = c.TABLE_SCHEMA
            and t.TABLE_NAME = c.TABLE_NAME
    where 1 = 1
        and lower(t.TABLE_TYPE) = 'base table'
        and c.DATA_TYPE not in ('image', 'sql_variant')
        and c.COLUMN_NAME like case when len(@ColumnName) > 0 then @ColumnName else '%' end
        and c.TABLE_NAME like case when len(@TableName) > 0 then @TableName else '%' end
    order by c.TABLE_NAME, c.ORDINAL_POSITION

declare
      @table_schema sysname
    , @table_name sysname
    , @column_name sysname
    , @data_type sysname
    , @exists nvarchar(max) -- Can be max for SQL2005+
    , @sql nvarchar(max) -- Can be max for SQL2005+
    , @where nvarchar(max) -- Can be max for SQL2005+
    , @run nvarchar(max) -- Can be max for SQL2005+

while exists (select null from @TabCols) begin

    select top 1
          @table_schema = table_schema
        , @table_name = table_name
        , @exists = 'select null from [' + table_schema + '].[' + table_name + '] where 1 = 0'
        , @sql = 'select ''' + '[' + table_schema + '].[' + table_name + ']' + ''' as TABLE_NAME, * from [' + table_schema + '].[' + table_name + '] where 1 = 0'
        , @where = ''
    from @TabCols
    order by id

    while exists (select null from @TabCols where table_schema = @table_schema and table_name = @table_name) begin

        select top 1
              @column_name = column_name
            , @data_type = data_type
        from @TabCols
        where table_schema = @table_schema
            and table_name = @table_name
        order by id

        -- Special case for money
        if @data_type in ('money', 'smallmoney') begin
            if isnumeric(@SearchTerm) = 1 begin
                set @where = @where + ' or [' + @column_name + '] = cast(''' + @SearchTerm + ''' as ' + @data_type + ')' -- could also cast the column as varchar for wildcards
            end
        end
        -- Special case for xml
        else if @data_type = 'xml' begin
            set @where = @where + ' or cast([' + @column_name + '] as nvarchar(max)) like ''' + @SearchTerm + ''''
        end
        -- Special case for date
        else if @data_type in ('date', 'datetime', 'datetime2', 'datetimeoffset', 'smalldatetime', 'time') begin
            set @where = @where + ' or convert(nvarchar(50), [' + @column_name + '], 121) like ''' + @SearchTerm + ''''
        end
        -- Search all other types
        else begin
            set @where = @where + ' or [' + @column_name + '] like ''' + @SearchTerm + ''''
        end

        delete from @TabCols where table_schema = @table_schema and table_name = @table_name and column_name = @column_name

    end

    set @run = 'if exists(' + @exists + @where + ') begin ' + @sql + @where + ' print ''' + @table_name + ''' end'
    print @run
    exec sp_executesql @run

end

set nocount off