Monday, November 23, 2015

Database Size Script

This is just a fancier version of sp_spaceused which writes the data to a temp table which you can sort on
use ManGroup
set nocount on
go

declare 
    @TableName varchar(128),
    @RID int,
    @MaxRID int,
    @SQL nvarchar(max)
if object_id('tempdb.dbo.#LoopSrc') is not null drop table #LoopSrc
create table #LoopSrc
(
    RID int identity(1,1) primary key clustered,
    TableName varchar(128)
)

if object_id('tempdb.dbo.#Tabs') is not null drop table #Tabs
create table #Tabs
(
    TableName varchar(128),
    nRows int,
    nReserved as cast(replace(sReserved, ' KB', '') as int),
    nData as cast(replace(sData, ' KB', '') as int),
    nIndexSize as cast(replace(sIndexSize, ' KB', '') as int),
    nUnused as cast(replace(sUnused, ' KB', '') as int),
    sReserved varchar(30),
    sData varchar(30),
    sIndexSize varchar(30),
    sUnused varchar(30)

)
/*****************************
*** INSERT LOOP ITEMS HERE ***
*****************************/
insert into #LoopSrc
(
    TableName
)
select name
from sys.tables

select 
    @RID = 1,
    @MaxRID = @@rowcount
/**********************
*** LOOP STRUCTURE  ***
**********************/
while @RID <= @MaxRID
    begin

        select 
            @TableName = TableName,
            @sql = 'exec sp_spaceused ' + TableName
        from #LoopSrc
        where RID = @RID
        begin try
            insert into #Tabs
            exec sp_executesql @sql
        end try 
        begin catch
        end catch

        select @RID += 1
    
    end


select *
from #Tabs
order by nRows desc

Tuesday, March 24, 2015

Script Out SQL Row Constructors

A while back I got tired of having to type out the long strings to script out rows from a table, so I built a snippet which does it for you. It’s almost illegible to read, but it determines the columns of the table you choose and builds a string to output each row. I'll admit that this could be further refined to add in some more precise data type handling (perhaps casting date fields to a certain varchar format, or handling more obscure data types), but for most every day purposes, this does just fine. Also, when you copy/paste the results into a query window, you'll have to manually cut out the first "union all" to get it to run. These omissions are just me being lazy, but there's no reason they can't be done, and if you want to make them or wrap this in a procedure, I encourage you to do so.

use 
go
set nocount on
go

declare @string nvarchar(max) = 
(
    --This creates a framework for each column in the chosen table, which then gets serialized and applied to each row of the selected table
    select ''' + '''
        + quotename(column_name) 
        + case when data_type in ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'date', 'time', 'datetime', 'datetime2', 'datetimeoffset')
               then ' = '' + isnull(quotename(cast(' 
                    + quotename(column_name) 
                    + ' as varchar(8000)), ''''''''), ''NULL'') + '', '
               else ' = '' + isnull(cast('
                    + quotename(column_name)
                    + ' as varchar(8000)), ''NULL'') + '', '
          end
    from .information_schema.columns
    where table_name = ''
        and column_name not in ('InsertDate', 'UpdateDate')
    for xml path('')
)

-- This dynamically selects those columns from the chosen table and with any optional where clause, and outputs the rows you can copy/paste into  query window
select @string = '
    select 
        ''union all select ' 
        + left(@string, len(@String) - 5) -- chop of leading ' + '
        + ' from .dbo. '
        + ' '

exec sp_executesql @string

Monday, January 12, 2015

Ambiguous IN functionality

Here is an interesting item that ended up biting me last week. This is definitely unexpected behavior from my understanding, but I wanted to make sure others were aware to avoid the problems. This code assumes you have a tally table called dbo.numbers with a single integer column named [Num].
--Set up a Temp Table so that we can limit the information we are getting out
If OBJECT_ID('tempdb..#NUMCheck') IS NOT NULL DROP TABLE #NUMCheck
Create Table #NUMCheck
(Number Int)

--a Simple IN using a Subquery
Select top 100 * from dbo.Numbers
where Num in (select Num From #NUMCheck )

--Oops, i forgot to load anything into the Table
Insert Into #NUMCheck
(Number)
Select 2
Union all Select 3
Union all Select 5
Union all select 7
Union all Select 11


--OK, try it again
Select top 100 * from dbo.Numbers
where Num in (select Num From #NUMCheck )

--?? Oh wait, the Temp Table uses Number not Num
--and how it should be written anyway
Select top 100 * from dbo.Numbers
where Num in (select Number From #NUMCheck)

--So it looks like if the Column does not exist in the Temp table, it will just pull from the FROM table
--No Comment on that the Column does not Exist
--No Error at Run time stating that the Column does not exist in the Temp table in the Sub Query

--But what if it did have the Same Name
If OBJECT_ID('tempdb..#NUMCheck2') IS NOT NULL DROP TABLE #NUMCheck2
Create Table #NUMCheck2
(Num Int)

Insert Into #NUMCheck2
(Num)
select 7
Union all Select 11

--and try it again
Select top 100 * from dbo.Numbers
where Num in (select Num From #NUMCheck2 )
--Why would that work, shouldn't it state that it was an Ambiguous column

/*
Select top 100 * 
from dbo.Numbers a
Inner Join #NUMCheck2 b
    on Num = Num
    
--well At least that Still works    
*/    


--so a slip up of the Column name in a Sub query can run without telling you it is an Issue
--Imagine this in a Delete Statement…