-- sql script to generate trigger for all existing table

       /* 
Parameters 
@Schemaname            - SchemaName to which the table belongs to. Default value 'dbo'. 
@DbName                 -database name where data need to be insert(log data store tabale)
@Tablename            - TableName for which the procs needs to be generated. 
@GenerateScriptOnly - When passed 1 , this will generate the scripts alone.. 
                      When passed 0 , this will create the audit tables and triggers in the current database. 
                      Default value is 1 
*/

SET NOCOUNT OFF

DECLARE @table NVARCHAR(128)
DECLARE @SQLTrigger NVARCHAR(MAX)
DECLARE @Schemaname SYSNAME = 'dbo' -- schema name
       ,@DbName SYSNAME = 'dbname' -- name of the database in which trigger is going to generate.
       ,@Tablename SYSNAME
       ,@GenerateScriptOnly BIT = 1 -- whether generate script only or execute 1 for script only,0 for to run directly.

DECLARE AllTables CURSOR
FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'

OPEN AllTables

FETCH NEXT
FROM AllTables
INTO @table

WHILE @@fetch_status = 0
BEGIN
       SELECT @Tablename = @table

       SELECT @SQLTrigger = '-- =============================================
-- Author:           Programerzone
-- Create date: '+CAST(GETDATE() AS NVARCHAR(256))+'
-- Description: Trigger for ' + @TableName + '
-- =============================================
'

       SELECT @SQLTrigger = @SQLTrigger + ' CREATE TRIGGER TR_' + @Tablename + ' ON dbo.' + @TableName + '
        FOR INSERT, UPDATE, DELETE
       AS
       IF (SELECT COUNT(*) FROM inserted) > 0
    BEGIN
        IF (SELECT COUNT(*) FROM deleted) > 0
        BEGIN
            -- update!
            INSERT INTO ' + @DbName + '.' + @Schemaname + '.' + @Tablename + ' SELECT *,''Update''
            FROM inserted
        END
        ELSE
        BEGIN
            -- insert!
             INSERT INTO ' + @DbName + '.' + @Schemaname + '.' + @Tablename + ' SELECT *,''Insert''
            FROM inserted
        END
    END
    ELSE
    BEGIN
        -- delete!
         INSERT INTO ' + @DbName + '.' + @Schemaname + '.' + @Tablename + ' SELECT *,''Delete''
            FROM deleted
    END'

       IF @GenerateScriptOnly = 1
       BEGIN
              PRINT @SQLTrigger
              PRINT 'GO'
       END
       ELSE
       BEGIN
              PRINT 'Creating Audit Trigger TR_' + @Tablename + ' for ' + @Schemaname + '.' + @Tablename

              EXEC (@SQLTrigger)

              PRINT 'Trigger ' + @Schemaname + '.' + 'TR_' + @Tablename + ' Created succefully'
       END

       FETCH NEXT
       FROM AllTables
       INTO @table
END

CLOSE AllTables


DEALLOCATE AllTables
According to MSDN, OPENDATASOURCE Provides ad hoc connection information as part of a four-part object name without using a linked server name.

The syntax is:- OPENDATASOURCE ( provider_name, init_string ) click here to know more about this.First You need to enable Ad Hoc Distributed Queries component of SQL Server.


sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Then also enable Allow Inprocess property of SQL SERVER NATIVE CLIENT SQLNCL from Server Objects/Linked Server/Provider in SSMS.Then run your sql query as

SELECT *
FROM   OPENDATASOURCE('SQLNCLI', 'Data Source=servername;user id=username;password=password')
.[databasename].schemaname.tablename

OR, you can pass dyanamic parameter by using dyanamic sql query as

DECLARE @server nvarchar(256)='servername'
EXEC ('
SELECT *
FROM   OPENDATASOURCE(''SQLNCLI'', ''Data Source='+@server+';user id=username;password=password'')
.[databasename].schemaname.tablename')

Using the OpenDataSource function is the same as using a four-part name to access a Linked Server, except the OpenDataSource() function defines the link within the function instead of referencing a predefined linked server. While defining the link in code bypasses the linked server requirement, if the link location changes, then the change will affect every query that uses OpenDataSource(). In addition, OpenDataSource() will not accept variables as parameters.




List all columns of specified table in a database in SQL SERVER

SELECT isc.COLUMN_NAME
       ,DATA_TYPE
       ,CHARACTER_MAXIMUM_LENGTH
       ,c.is_identity
       ,c.is_nullable
       ,CASE
              WHEN ccu.COLUMN_NAME IS NULL
                     THEN 0
              ELSE 1
              END AS is_primary
FROM INFORMATION_SCHEMA.COLUMNS isc
INNER JOIN [sys].[columns] c ON isc.COLUMN_NAME = c.NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON tc.TABLE_NAME = isc.TABLE_NAME
       AND tc.CONSTRAINT_TYPE = 'Primary Key'
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
       AND ccu.COLUMN_NAME = isc.COLUMN_NAME
WHERE isc.TABLE_NAME = 'aspnet_users'
       AND c.OBJECT_ID = OBJECT_ID(isc.TABLE_NAME)

The output is:-


Pad a string with leading zeros in SQL Server

The problem is:-

I have a string that is up to 5 characters long when its first created in SQL Server 2008 R2.
I would like to pad it with leading zeros, so if its original value was '1' then new value would be '00001' Or if its original value was '23' the new value is '00023' OR if its original value is '12345' then new value is the same as original value.
Solution is:-
If the field is already a string, this will work
 SELECT RIGHT('00000'+ISNULL(field,''),5)
If you want nulls to show as '00000'
It might be an integer -- then you would want
 SELECT RIGHT('00000'+CAST(field AS VARCHAR(5)),5) 
OR
SELECT REPLACE(STR(field ,5),' ','0')
Reference taken from StackOverFlow.
Today, I am going to share you the script that generate CRUD script that create four stored procedure of any table for insert, update, select and delete.You need to have a identity column on a table to work this SQL script properly.You need to pass name of the table that you want to create stored procedure in @InsertSelectblName variable and @IsExecute is need to set 1 to execute this generated script directly and 0 to print only, Which is set 0(false) by default.


-- ================================================
-- CRUD script generater writtern by Kamal Khanal
-- http://programerzone.blogspot.com
-- ================================================

SET NOCOUNT ON;
PRINT '-- ================================================'
PRINT '-- CRUD script generater writtern by Kamal Khanal'
PRINT '-- http://programerzone.blogspot.com'
PRINT '-- ================================================'
DECLARE @InsertSelectblName NVARCHAR(256)
DECLARE @IsExecute BIT

SELECT @InsertSelectblName='yourtablename' --name of the table to generated crud script

SELECT @IsExecute=0-- whether execute this script or just print

IF OBJECT_ID('tempdb..#tmptablcol') IS NOT NULL
    DROP TABLE #tmptablcol

SELECT c.column_name,data_type,character_maximum_length,is_nullable, isnull(is_identity,0) as is_identity  INTO #tmptablcol
FROM INFORMATION_SCHEMA.COLUMNS c
       left join ( select name,is_identity from sys.columns where object_id= object_id(@InsertSelectblName)) const on const.name=c.COLUMN_NAME
WHERE c.TABLE_NAME=@InsertSelectblName


DECLARE @column_name NVARCHAR(256),
@data_type NVARCHAR(256),
@character_maximum_length INT,
@is_nullable NCHAR(5),
@is_identity BIT

DECLARE @insertcolumn_sql NVARCHAR(MAX)=''
DECLARE @UpdateSelectpdatecolumn_sql NVARCHAR(MAX)=''
DECLARE @select_sql NVARCHAR(MAX)=''
DECLARE @insert_sql NVARCHAR(MAX)=''
DECLARE @update_sql NVARCHAR(MAX)=''
DECLARE @delete_sql NVARCHAR(MAX)=''
DECLARE @identity_col NVARCHAR(50)

DECLARE db_cursor CURSOR FOR 
SELECT *
FROM #tmptablcol

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @column_name ,
@data_type,
@character_maximum_length,
@is_nullable,
@is_identity
WHILE @@FETCH_STATUS =
BEGIN 
if(@is_identity=1)
begin
SELECT @identity_col=@column_name
SELECT @UpdateSelectpdatecolumn_sql+='
        @'+@column_name+' '+@data_type
       if(@character_maximum_length is not null)
       select @UpdateSelectpdatecolumn_sql+='('+cast(@character_maximum_length as nvarchar)+')'
       select @UpdateSelectpdatecolumn_sql+=' = NULL,'
end
else
begin
SELECT @insertcolumn_sql+='
@'+@column_name+' '+@data_type
if(@character_maximum_length is not null)
       select @insertcolumn_sql+='('+cast(@character_maximum_length as nvarchar)+')'

       if(@is_nullable='YES')
       select @insertcolumn_sql+=' = NULL,'
       else
       select @insertcolumn_sql+=','

end
       FETCH NEXT FROM db_cursor INTO @column_name ,
@data_type,
@character_maximum_length,
@is_nullable,
@is_identity
END 
CLOSE db_cursor 
DEALLOCATE db_cursor
select @insertcolumn_sql=substring(@insertcolumn_sql,1,len(@insertcolumn_sql)-1)

DECLARE @InsertSelect NVARCHAR(MAX)
DECLARE @InsertSelectVal NVARCHAR(MAX)
DECLARE @UpdateSelect NVARCHAR(MAX)

select @InsertSelect=  COALESCE(@InsertSelect + ',', '') + '['+ COLUMN_NAME+']' FROM #tmptablcol WHERE is_identity=0
select @InsertSelectVal=  COALESCE(@InsertSelectVal + ',', '') + '@'+ COLUMN_NAME FROM #tmptablcol WHERE is_identity=0
select @UpdateSelect=  COALESCE(@UpdateSelect + ',', '') + '['+ COLUMN_NAME+'] = @'+COLUMN_NAME FROM #tmptablcol WHERE is_identity=0

SELECT @insert_sql= '--insert script for table '+@InsertSelectblName

select @insert_sql+='
CREATE PROCEDURE usp_add'+@InsertSelectblName+'
'+@insertcolumn_sql+'     
AS
BEGIN
       SET NOCOUNT ON;

       INSERT INTO '+@InsertSelectblName+'('+@InsertSelect+')
       VALUES('+@InsertSelectVal+')'+'

END

'
SELECT @update_sql+= '--update script for table '+@InsertSelectblName

SELECT @update_sql+='
CREATE PROCEDURE usp_update'+@InsertSelectblName+'
'+@UpdateSelectpdatecolumn_sql+@insertcolumn_sql+'    
AS
BEGIN
       SET NOCOUNT ON;

       UPDATE '+@InsertSelectblName+' SET '+@UpdateSelect+'
       WHERE ['+@identity_col+'] =@'+@identity_col+'

END

'
SELECT @select_sql+=  '--select script for table '+@InsertSelectblName

SELECT @select_sql+='
CREATE PROCEDURE usp_select'+@InsertSelectblName+'
'+substring(@UpdateSelectpdatecolumn_sql,1,len(@UpdateSelectpdatecolumn_sql)-1)
+'    
AS
BEGIN
       SET NOCOUNT ON;

       SELECT '+@InsertSelect+' FROM '+@InsertSelectblName+'
       WHERE (['+@identity_col+'] =@'+@identity_col+' OR @'+@identity_col+' IS NULL)'+'

END

'
SELECT @delete_sql+=  '--delete script for table '+@InsertSelectblName

SELECT @delete_sql+='
CREATE PROCEDURE usp_delete'+@InsertSelectblName+'
'+substring(@UpdateSelectpdatecolumn_sql,1,len(@UpdateSelectpdatecolumn_sql)-1)
+'    
AS
BEGIN
       SET NOCOUNT ON;

       DELETE FROM '+@InsertSelectblName+'
       WHERE ['+@identity_col+'] =@'+@identity_col+'

END

'
IF( @IsExecute=1)
BEGIN
EXEC (@insert_sql)
EXEC (@update_sql)
EXEC (@select_sql)
EXEC (@delete_sql)
END
ELSE
BEGIN
PRINT @insert_sql
PRINT @update_sql
PRINT @select_sql
PRINT @delete_sql
END

Just copy above sql script or download script file here Click Here To Download

Blogger Templates by Blog Forum