How to ALTER all tables columns in SQL Database

declare @cols table (i int identity, tablename varchar(100), colname varchar(100))

insert into @cols
select TABLE_NAME, COLUMN_NAME
from information_schema.COLUMNS
where DATA_TYPE='nvarchar'

select * from @cols

declare @i int, @maxi int
select @i = 1, @maxi = MAX(i) from @cols

declare @sql nvarchar(max)

while(@i <= @maxi)
begin
    select @sql = 'alter table sub_history.'+tablename+' alter column ' + colname + ' varchar(255) NULL'  from @cols where i = @i
    exec sp_executesql @sql
    select @i = @i + 1
end

Exception- The definition for user-defined data type ‘MyTypeList’ has changed.

The definition for user-defined data type ‘MyTypeList’ has changed. Exception when we rename or changed any User defined TYPE and it used in stored procedures.
Stored Procedures which is using this type variable will not run and throwing the exception. If we have large numbers of SP then it will to time consuming or irritating to re-compile all SP one by one.
Instead of using this manual process this script will help to fix all references.

DECLARE @Name NVARCHAR(776);

DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.MyTypeList', 'TYPE');

OPEN REF_CURSOR;

FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
	PRINT @Name
    FETCH NEXT FROM REF_CURSOR INTO @Name;
END;

CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;

Search in MSSQL tables

Sometime we need to reverse engineering in the database tables to find a specific values, we can find the column name easily but in large database, to search a specific key value is too hard. From below query we can easily search value in all tables, it will list column with table in the result.

DECLARE @SearchStr nvarchar(100) = 'test'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal', 'numeric')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT distinct ColumnName, ColumnValue FROM @Results order by ColumnValue

How to read DBF to Datatable

OleDbConnection oConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Temp;Extended Properties=dBase III");
OleDbCommand command = new OleDbCommand("SELECT * FROM Test.DBF", oConn);
oConn.Open();
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
oConn.Close();

SQL SERVER – Installation Failure Database engine services failed.

Sometimes when we install SQL Server or Add any new SQL Server Instance on Windows Machine, we can see the database errors listed below.
To resolve these errors we are trying to repair existing installation or do something else.
If nothing happens then we are trying to install it completely from control panel, but it is not 100% sure that the fresh new installation will works. Some of the SQL Server links existing at system registry so we are not able to get it success.

Installation error list
Installation error

Installation error list
Installation error list

To resolve these registry things we can un-install all SQL server related things by theirs ID.

The ID we can get using “WMIC” command. So run this command on console:

WMIC PRODUCT Where "Caption like '%SQL%'" GET Caption, IdentifyingNumber

WMIC Result
WMIC Result

You will get a list of objects with their identifier.

Now un-install these all things one by one using below command:

msiexec /x {GUID}

i.e..

msiexec /x {ABB6AC00-F1D8-4EBF-8128-830D090B76C0}

 

I think after that restart the machine and try fresh installation, will works.

Import CSV using “Microsoft Access Database Engine 2010” to SQL Server


sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure with override

SET NOCOUNT ON

EXEC master..xp_cmdshell 'del C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo [Test.txt] > C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo ColNameHeader=True >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Format=Delimited(^|) >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo MaxScanRows=50 >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo CharacterSet=OEM >> C:\Temp\Schema.ini'

EXEC master..xp_cmdshell 'echo Col1=ID TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col2=LastName TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col3=FirstName TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col4=MiddleName TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col5=MailingAddressLine1 TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col6=MailingAddressline2 TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col7=MailingAddressCity TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col8=MailingAddressState TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col9=MailingAddressZIP TEXT >> C:\Temp\Schema.ini'

EXEC ('INSERT INTO ImportTest (ID,LastName, FirstName,MiddleName, MailingAddressLine1,MailingAddressline2,MailingAddressCity, MailingAddressState,MailingAddressZIP)
SELECT ID,LastName, FirstName,MiddleName, MailingAddressLine1,MailingAddressline2,MailingAddressCity, MailingAddressState,MailingAddressZIP
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Text;Database=C:\Temp\;HDR=YES'',''select * from person.txt'')
')
EXEC master..xp_cmdshell 'del C:\Temp\Schema.ini'

How to Backup Microsoft SQL Server (MSSQL) Databases

--1. Variable declaration

DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @filename VARCHAR(256)
 
-- 2. Setting the backup path

SET @path = 'X:\MSSQLDB\Backup\'  

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- ignore system databases

--3. Initializing cursor operations

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN

  -- 4. Defining the filename
    SET @fileName = @path + @name + '.BAK'  
    BACKUP DATABASE @name TO DISK = @fileName 

    FETCH NEXT FROM db_cursor INTO @name   
END   
CLOSE db_cursor   
DEALLOCATE db_cursor

 

How to Create Multiple SQL Server Instances on Same Server

When you install SQL Server, the installation creates an instance (DEFAULT OR SQLEXPRESS) of SQL Server. In my case I have created instanace named DEVSQL.

Some times we do need to create multiple instances based on requirements. To create more instances, start the setup again. The following screen appears when setup starts for the SQL Server 2012.

Click “New SQL Server stand-alone installation or add features to an existing installation”, the following screen appears:

Installation Start

As you can see DEVSQL instance already exists, it was created by the first installation, now select “New installation or add shared features”. Then click Next and accept the License Agreement.

Then select features you want to install for the new instance.

After that the installation offer you to specify the name of your new instance (MYTEST was chosen in this example):

Click Next to create the new instance and perform the remaining setup steps.

Now you are done!