Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=11.0.0.0,

Clear all objects from database

Today I have been working on web deploy solution, and needed to clear databse.
Drop database does not work for me, because the files still hang around. And I want to reuse the implementation for every build I do. This solution come from the need of regular deploy, from my build server.


I need to clear:

1. Stored procedures if any
2. Foreign keys
3. Primary key constaints
4. Tables

My implementation works with only [dbo] schema, but you can specify the block for each chema you need.

Original source for this you can find of course on Stack overflow

My modification is make sure that database name is specified for every schema, in order to satisfy DB admins, that in case of selecting wrong schmea, the implementation does not remove everything.

Now SQL:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'HomeSite')
BEGIN

    /* ====================================================*/
    /*           Drop all non-system stored procs          */
    /* ====================================================*/
        
        /* Drop all non-system stored procs */
        DECLARE @StoredProcName VARCHAR(128)
        DECLARE @StoredProcSQL VARCHAR(254)

        SELECT @StoredProcName = (SELECT TOP 1 [name] FROM HomeSite..sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

        WHILE @StoredProcName is not null
        BEGIN
            SELECT @StoredProcSQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@StoredProcName) +']'
            EXEC (@StoredProcSQL)
            PRINT 'Dropped Procedure: ' + @StoredProcName
            SELECT @StoredProcName = (SELECT TOP 1 [name] FROM HomeSite..sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @StoredProcName ORDER BY [name])
        
        END
        
    /* ====================================================*/
    /*           Drop all Foreign Key constraints          */
    /* ====================================================*/        
        
        DECLARE @ForeignKeyName VARCHAR(128)
        DECLARE @ForeignKeyConstraint VARCHAR(254)
        DECLARE @ForeignKeySQL VARCHAR(254)

        SELECT @ForeignKeyName = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

        WHILE @ForeignKeyName is not null
        BEGIN
            SELECT @ForeignKeyConstraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @ForeignKeyName ORDER BY CONSTRAINT_NAME)
            WHILE @ForeignKeyConstraint IS NOT NULL
            BEGIN
                SELECT @ForeignKeySQL = 'ALTER TABLE [dbo].[' + RTRIM(@ForeignKeyName) +'] DROP CONSTRAINT [' + RTRIM(@ForeignKeyConstraint) +']'
                EXEC (@ForeignKeySQL)
                PRINT 'Dropped FK Constraint: ' + @ForeignKeyConstraint + ' on ' + @ForeignKeyName
                SELECT @ForeignKeyConstraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @ForeignKeyConstraint AND TABLE_NAME = @ForeignKeyName ORDER BY CONSTRAINT_NAME)
            END
        SELECT @ForeignKeyName = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
        END
    
    /* ====================================================*/
    /*           Drop all Primary Key constraints          */
    /* ====================================================*/
    
        DECLARE @PrimaryKeyName VARCHAR(128)
        DECLARE @PrimaryKeyConstraint VARCHAR(254)
        DECLARE @PrimaryKeySQL VARCHAR(254)

        SELECT @PrimaryKeyName = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

        WHILE @PrimaryKeyName IS NOT NULL
        BEGIN
            SELECT @PrimaryKeyConstraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @PrimaryKeyName ORDER BY CONSTRAINT_NAME)
            WHILE @PrimaryKeyConstraint is not null
            BEGIN
                SELECT @PrimaryKeySQL = 'ALTER TABLE [dbo].[' + RTRIM(@PrimaryKeyName) +'] DROP CONSTRAINT [' + RTRIM(@PrimaryKeyConstraint)+']'
                EXEC (@PrimaryKeySQL)
                PRINT 'Dropped PK Constraint: ' + @PrimaryKeyConstraint + ' on ' + @PrimaryKeyName
                SELECT @PrimaryKeyConstraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @PrimaryKeyConstraint AND TABLE_NAME = @PrimaryKeyName ORDER BY CONSTRAINT_NAME)
            END
        SELECT @PrimaryKeyName = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
        END
        
    
        
    /* ====================================================*/
    /*           Finally drop all tables                           */
    /* ====================================================*/
        DECLARE @TableName VARCHAR(128)
        DECLARE @TableSQL VARCHAR(254)

        SELECT @TableName = (SELECT TOP 1 [name] FROM HomeSite..sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

        WHILE @TableName IS NOT NULL
        BEGIN
            SELECT @TableSQL = 'DROP TABLE [dbo].[' + RTRIM(@TableName) +']'
            EXEC (@TableSQL)
            PRINT 'Dropped Table: ' + @TableName
            SELECT @TableName = (SELECT TOP 1 [name] FROM HomeSite..sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @TableName ORDER BY [name])
        END    
    
END
GO

Database diagram support objects cannot be installed

Database diagram support objects cannot be installed because this database does not have a valid owner

The error

"Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."

The fix

The information provided by SSMS was very helpful but anyway.

Reason
the database has lost its owner and needs to be corrected, simply done with:

ALTER AUTHORIZATION ON DATABASE::databasename TO sa 

replace sa and databasename with your variables

SQL Profiler who is using SPID

We have had multiple applications querying database and we needed to identify where they are comming from.

To identify pc where the call originated you can use sql command.

SELECT * FROM sysprocesses WHERE spid = 56

NOTE: You have to run in agains MASTER database

In response search for hostname.
The value could be somethind like: DESKTOP

MS SQL Cheat Sheet

My small MS SQL Cheat Sheet of sql commands

Find table by column name:

Exact match on column name:

SELECT 
  * 
FROM 
   sysobjects 
WHERE 
   id IN
    (SELECT id FROM syscolumns WHERE name ='your column name')


Part of column name to search:

SELECT 
  * 
FROM 
   sysobjects 
WHERE 
   id IN
    (SELECT id FROM syscolumns WHERE name LIKE '%your column name%')

MS SQL 2000 transacion

in this version of sql the transaction is just beginning and is not as clear as it is in later versions of sql. To create transaciton we need to use command TRAN. Which later is used as TRANSACTION.

SQL Search for table name in database by part of table name

SELECT
*
FROM
information_schema.tables
WHERE
table_name like '%partOFTableName%'

Select into another table

SELECT Suppliers.Name, Product, Products.UnitPrice
INTO [Mexican Suppliers]
FROM Suppliers

Select rows with repeating value

If you need to get all values that are used more than once in table you can use following query:

  SELECT
    [column],
    count([column]) 'Count'
    FROM
    [TSTT_Subscribers].[dbo].[tblGSM_subscribers]
    GROUP BY [column]
    HAVING COUNT([column]) > 1

Search for text in all stored procedures for specific database

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Searched_Text_InsertHere%'
AND ROUTINE_TYPE='PROCEDURE'

Get all parameters for stored procedure

ALTER procedure [sys].[sp_procedures_rowset]
(
    @procedure_name     sysname,
    @group_number       int = 1,
    @procedure_schema   sysname = null
)
as
    select
        PROCEDURE_CATALOG       = db_name(),
        PROCEDURE_SCHEMA        = schema_name(pro.schema_id),
        PROCEDURE_NAME          = convert(nvarchar(134),pro.name +';'+ ltrim(str(pro.procedure_number,5))),
        PROCEDURE_TYPE          = convert(smallint, 3), -- DB_PT_FUNCTION
        PROCEDURE_DEFINITION    = convert(nvarchar(1),null),
        DESCRIPTION             = convert(nvarchar(1),null),
        DATE_CREATED            = pro.create_date,
        DATE_MODIFIED           = convert(datetime,null)
    from
        sys.spt_all_procedures pro
    where
        (
            (@procedure_schema is null and pro.name = @procedure_name) or
            object_id(quotename(@procedure_schema) + '.' + quotename(@procedure_name)) = pro.object_id
        ) and
        (@group_number is null or pro.procedure_number = @group_number)
    order by 2, 3

What is file location of database

I needed to find out location of the db files (mdf, ldf). I have used following command to get that information:


SELECT name, physical_name AS current_file_location
FROM sys.master_files

Working with cursors

Here is example of using cursor

code:

DECLARE @processingId int =0
DECLARE ExistingRequests_Cursor CURSOR FOR
SELECT primaryKey FROM table
OPEN ExistingRequests_Cursor
FETCH NEXT FROM ExistingRequests_Cursor INTO @processingId  
WHILE @@FETCH_STATUS = 0
BEGIN


 SELECT @processingId
   END  
  
   FETCH NEXT FROM ExistingRequests_Cursor
END
CLOSE ExistingRequests_Cursor
DEALLOCATE ExistingRequests_Cursor

Copy one column of table into another

Memory glitch? Here you are

UPDATE <tablename>
SET <destination column name> = <source column name>

Truncate table

Want to delete table fast? use command:

truncate table replaceWithYourTableName

Display information into console

run command: 

PRINT 'Hello, world!'

  Get All talbes that are for specific database

Each Database have its own sys.objects, so you have to get the databases from sys.databases, and

select from {dbname}.sys.objects

Example : SELECT * FROM HomeSite..sysobjects WHERE [type] = 'U'