Database Size Calculations

This post falls firmly in the category of blocking and tackling, but is a question we all have to answer from time to time: “How big is this database?”

Two methods:

SELECT
    DB_NAME(database_id) AS DatabaseName
  , Name AS Logical_Name
  , Physical_Name
  , ( CAST( SIZE AS BIGINT ) * 8 ) / 1024 / 1024 SizeGB
FROM
    sys.master_files

or

EXEC sp_helpdb

Both will get you size per database. The former allows you to see the breakdown of all files (the cast to BIGINT is for really large databases), and the latter returns the sum of all data and log files per database.

Posted in SQL Server Development, SQL Toolbox | Leave a comment

DQS Services in Denali

Interesting article about the upcoming Data Quality Services feature in Denali. It’s worth mentioning, as it’s very similar to the Data Quality Control system that I developed (with a little inspiration from my friend) for our current data mart.

In short, what I do is run a series of data quality tests on the data every time it loads. These can then be flagged for follow-up by end-users, thus increasing the overall quality of the data in the data mart.

While the feature-set is not nearly as complete as the Microsoft version, it’s fun seeing something you worked on reflected in Sql Server.

Posted in Uncategorized | Leave a comment

XML Schema Creation in SQL Server 2005

Here’s an interesting approach for creating a quick and dirty schema to get you started with XML.

DECLARE @schema xml
SET @schema = (SELECT * FROM schema.TABLE FOR XML AUTO, ELEMENTS,
XMLSCHEMA('SchemaName'))
SELECT @schema

I don’t have the source, but I have where I found┬áit.

Posted in SQL Toolbox | Tagged , , | Leave a comment

T-SQL Alter Statement Quirk

To answer a recent question to the PSSUG user group e-mail, I put together a post instead of trying to answer via e-mail.

First, the question:

I have a staging table.

Stage_id identity
Stage_File_Name nvarchar(100)
AllData nvarchar(max)

I load a pipe delimited file into it. Placing the entire record into the nvarchar(max). This way I can load anything into it.

Instead of haveing a separate staging table for each differant file.

I am trying to Add the columns I need. Splitting out the file. Then moving it into the normal table for that file.

I have it working in a batch but cannot make it a stored procedure.

Alter table staging
Add colmumn A char(10),
B INT
C DATETIME
ETC…….

UPDATE staging
SET A = SS(ALLDATA,1,4)
B
C
ETC….

INSERT NORMAL TABLE (A,B,C,ETC…)
SELECT STAging

Alter table staging
drop column a,b,c,etc….

this works well until I try to make it so I can run it via stored procedure. Eventually I would like to create a job agent.

Any Ideas how I can make this work. Or am I going to have to write it to create a new staging table each time and drop it.

Here’s my (edited) answer, updated with working t-sql below.

…Assuming I understood your question correctly, I can give you a solution, but not an explanation. Perhaps someone else can chime in on the why.

If you wrap the alter statements (to add the columns) and update statements (on the new columns) in separate strings, and execute using the EXEC command, you can wrap it in a stored procedure. …

Here is the setup, with the script I whipped up based on the question.

CREATE DATABASE Testing_AlterStatementFun
GO

USE Testing_AlterStatementFun
GO

IF OBJECT_ID('Foo') IS NOT NULL
 DROP TABLE Foo

CREATE TABLE Foo
(
    PK INT IDENTITY(1,1)
    , FK INT NOT NULL
    , BigData VARCHAR(MAX) NOT NULL
)

INSERT INTO Foo ( FK, BigData ) VALUES ( 1, 'abcdefghijklmnopqrstuvwxyz' )


ALTER TABLE Foo
    ADD First5 VARCHAR(5)
    , Last5 VARCHAR(5)
   
UPDATE Foo
SET First5 = LEFT( BigData, 5 )

UPDATE Foo
SET Last5 = RIGHT( BigData, 5 )

The above code works as a script. Next, the part that doesn’t work. First, I recreate the table, since if you create Foo_Fail without doing that, it will see that table Foo has the referenced columns.

--Recreate and Load the table.
IF OBJECT_ID('Foo') IS NOT NULL
 DROP TABLE Foo

CREATE TABLE Foo
(
    PK INT IDENTITY(1,1)
    , FK INT NOT NULL
    , BigData VARCHAR(MAX) NOT NULL
)

INSERT INTO Foo ( FK, BigData ) VALUES ( 1, 'abcdefghijklmnopqrstuvwxyz' )

-- Can't create it, since First5 doesn't exist.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[Foo_Fail]') AND OBJECTPROPERTY(Object_id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[Foo_Fail]
GO

CREATE PROCEDURE [dbo].[Foo_Fail]
AS

    ALTER TABLE Foo
        ADD First5 VARCHAR(5)
        , Last5 VARCHAR(5)
       
    UPDATE Foo
    SET First5 = LEFT( BigData, 5 )

    UPDATE Foo
    SET Last5 = RIGHT( BigData, 5 )

GO

As advertised, this fails.

However, this does function (it’s ugly, but it works).

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[Foo_Pass]') AND OBJECTPROPERTY(Object_id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[Foo_Pass]
GO


CREATE PROCEDURE [dbo].[Foo_Pass]
AS

EXEC( '
ALTER TABLE Foo
    ADD First5 VARCHAR(5)
    , Last5 VARCHAR(5)' )
   
EXEC( '
UPDATE Foo
SET First5 = LEFT( BigData, 5 )' )

EXEC( '
UPDATE Foo
SET Last5 = RIGHT( BigData, 5 )' )


GO

Finally, here’s the script working.

IF OBJECT_ID('Foo') IS NOT NULL
 DROP TABLE Foo

CREATE TABLE Foo
(
    PK INT IDENTITY(1,1)
    , FK INT NOT NULL
    , BigData VARCHAR(MAX) NOT NULL
)

INSERT INTO Foo ( FK, BigData ) VALUES ( 1, 'abcdefghijklmnopqrstuvwxyz' )

EXEC [dbo].[Foo_Pass]

And finally, cleanup your mess.

USE Master
GO
DROP DATABASE Testing_AlterStatementFun
GO
Posted in Uncategorized | Leave a comment

Recursive Directory Scripting

Inside source control, I have scripts arranged as follows:

Drive:/Base/Database/
Drive:/Base/Database/Functions
Drive:/Base/Database/Proc
Drive:/Base/Database/Tables
Etc..

Each script stored can be run multiple times with no ill effect. For example, a table generation script first checks to see that it exists before running a create.

I was troubleshooting an issue where it seemed the source and the database were out of sync, so I needed to push everything I had to a test database to continue my troubleshooting, and I didn’t want to run each script by hand.

I knew about sqlcmd, and wanted to quickly script out each file to run on the target database. I also needed a list of all the files in each of those layers of directories.

In the windows environment, it was surprisingly difficult to accomplish a recursive search for files to run, so I had to do some Google sleuthing to figure it out.

There were too many sources to link them all properly, but this was patched together from all over the web. If there’s a better method, I’d love to hear about it.

Here are the steps I followed:

- Open up a command prompt window
- Navigate to the root directory where my scripts are stored (in this example: Drive:/Base/Database/)
- run: for /r %d in (*.sql) do echo %d > sqlcmd_files.bat
(alternatively change > to >> to append to sqlcmd_files.bat instead of overwriting it)
- Edit sqlcmd_files.bat w/ your favorite text editor
- Find and Replace the Drive:\ with sqlcmd -Sserver_name -ddatabase_name -iDrive:\
(this should change your lines from Drive:/Base/Database/Proc/Filename.sql to sqlcmd -Sserver_name -ddatabase_name -iDrive:\Base/Database/Proc/Filename.sql )
- Save and execute.

Known limitations:
- Doesn’t take into account dependencies. If the tables happen to script out after a stored proc that uses that table sqlcmd will throw an error. In my case, I just ran the sqlcmd_files.bat twice. The first time laid down anything without dependencies, and the second time cleaned everything up.
- Specifically looks for files with the extension “.sql”. If you use something else, adjust accordingly.
- All of my paths have no spaces in them. If you do, consider wrapping in quotes around your paths.

Posted in SQL Server Development, SQL Toolbox | Tagged , , | Leave a comment

Primary, Secondary, Tertiary, umm…?

I recently found myself asking the question posed in the title – what comes after primary, secondary, tertiary…?

I had a rough idea, based on my drum line days, and the various types of Tenor drums.

However, this article lays it out nice and neat.
Source

1st = primary, 2nd = secondary, 3rd = tertiary, 4th = quaternary, 5th = quinary, 6th = senary, 7th = septenary, 8th = octonary, 9th = nonary, 10th = denary, 12th = duodenary and 20th = vigenary.

Personally, anything after you hit the 5th level, you might consider another pattern, since you’ll lose your audience, unless they all happen to speak Latin.

Posted in SQL Toolbox | Tagged , | Leave a comment

Moving Database Logins

Recently we had a database issue, that necessitated us moving logins.

Here’s an article I used from Microsoft to help script the logins. The KB is specifically for 2005 -> 2008, but it will also get you login’s out of a stricken server.

Source

It worked really well, and I wanted to record it here for reference.

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue VARCHAR (514) OUTPUT
AS
DECLARE @charvalue VARCHAR (514)
DECLARE @i INT
DECLARE @LENGTH INT
DECLARE @hexstring CHAR(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @LENGTH = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @LENGTH)
BEGIN
  DECLARE @tempint INT
  DECLARE @firstint INT
  DECLARE @secondint INT
  SELECT @tempint = CONVERT(INT, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO
 
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @TYPE VARCHAR (1)
DECLARE @hasaccess INT
DECLARE @denylogin INT
DECLARE @is_disabled INT
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  VARCHAR (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string VARCHAR (514)
DECLARE @tmpstr  VARCHAR (1024)
DECLARE @is_policy_checked VARCHAR (3)
DECLARE @is_expiration_checked VARCHAR (3)

DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.TYPE, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.TYPE IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.TYPE, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.TYPE IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @TYPE, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (VARCHAR, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@TYPE IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @TYPE, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Then

EXEC sp_help_revlogin
Posted in SQL Toolbox | Tagged , , , | Leave a comment

RePost: Database development mistakes made by application developers

Link

Personally, I think this should also include database developers operating on too little sleep and/or too close of a deadline.

Posted in SQL Server Development | Tagged | Leave a comment

Enable CLR

This is just one of those things that I always forget how to do.

EXEC sp_CONFIGURE 'SHOW ADVANCED OPTIONS' , '1' -- Let me change it
GO
RECONFIGURE
GO
EXEC sp_CONFIGURE 'CLR ENABLED' , '1'
GO
RECONFIGURE
GO
EXEC sp_CONFIGURE 'SHOW ADVANCED OPTIONS' , '0' -- Return to status quo
GO
RECONFIGURE
GO
Posted in SQL Toolbox | Tagged , | Leave a comment

T-SQL Creativity

This article has little to do with useful t-sql applications – since to me, minesweeper is meant for a mouse. However, it really demonstrates a fun use of t-sql.
Minesweeper in T-SQL

Posted in Uncategorized | Tagged , | Leave a comment