Ashish Agarwal

SELECT t . name AS [Table_Name] , c . name AS [Column_Name] , tp . name AS [Data_Type] , c . max_length AS [Size] FROM sys . tables t JOIN sys . columns c ON t . object_id = c . object_id JOIN sys . types tp ON c . system_type_id = tp . system_type_id WHERE c . is_identity = 1 and t . name not like 't_%' order by t . name

with duplicate as ( SELECT      row1 , row2 , row3 , row_Number () Over ( partition by row1 , row2 , row3 order by row1 ) as RowNumber FROM   YourTable ) delete from duplicate where RowNumber >= 2

DECLARE @commaSeperatedCols VARCHAR ( MAX ) DECLARE @TableName VARCHAR ( 100 ) SET @TableName = 'Your_Table_Name' SELECT @commaSeperatedCols = COALESCE ( @commaSeperatedCols + ',' , '' ) + COLUMN_NAME FROM INFORMATION_SCHEMA . COLUMNS WHERE TABLE_NAME = @TableName SELECT @commaSeperatedCols

Declare @i int , @loop int , @str varchar ( 100 ) = 'TEST_lOwerCase' IF EXISTS ( SELECT * FROM tempdb . dbo . sysobjects WHERE ID = OBJECT_ID ( N'tempdb..#Temp' ) ) BEGIN DROP TABLE #Temp END create table #Temp ( id int ) select @i = LEN ( @str ) print @i set @loop = 1 while ( @loop <= @i ) BEGIN       if (( ascii ( SUBSTRING ( @str , @loop , 1 ))>= 97 ) and ( ascii ( SUBSTRING ( @str , @loop , 1 ))<= 122 ))       insert into #Temp values ( @loop )       set @loop = @loop + 1 END select MIN ( id ) from #Temp GO

Hello Friends !! Today I am writing my first C#.Net Article going a bit out of my way from SQL and MSBI. This is something related to a requirement which comes my way when in a windows Application i need to give a drop down for all like customer names when typing the names in the Text Box as a very basic user friendly option. Here is how we can do this - Let's take as assumption as we have a table in SQL as CustomerTable and we want to take the FirstName of the Customer as our search result. we will write this code in the Form load event of the form which contains the Text box. The connectionStringName will be configured and can be get from the App.Config table. Also you need to change the below 2 properties of the Text Box on which you want to implement the Auto Complete functionality. SqlConnection  con = new  SqlConnection ( ConfigurationManager .ConnectionStrings[ "connectionStringName" ].ConnectionString); SqlCommand  cmd =  new...

Sometimes the requirement comes when we need to pump the data from one server to another server. Though we have SQL Import\Export wizard which can do this quiet easily, but in that way we will loose some important features of the table --      1. The Identity property of a column in case we have one in the table.      2. All the indexes. To avoid this we can go with a simple SSIS package to which we just need to pass the table name as a parameter and it will do the rest, taking care of the above constraints. This is how the Package looks - Steps - 1. To make the executable dynamic we will drive the package from a table, where we will    store the names of all the tables which we want to refresh from the source server and we will store it in a result set variable (Table_List) Refer the variables list in the below figure used in the package. 2. Create a For each ...

DECLARE IndexCursor CURSOR FOR SELECT OBJECT_NAME ( SI . Object_ID ), SI . Object_ID , SI . Name , SI . Index_ID FROM sys.indexes SI LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI . Name = TC . CONSTRAINT_NAME AND OBJECT_NAME ( SI . Object_ID ) = TC . TABLE_NAME WHERE TC . CONSTRAINT_NAME IS NULL AND OBJECTPROPERTY ( SI . Object_ID , 'IsUserTable' ) = 1 ORDER BY OBJECT_NAME ( SI . Object_ID ), SI . Index_ID DECLARE @IxTable varchar ( 50 ) DECLARE @IxTableID INT DECLARE @IxName varchar ( 50 ) DECLARE @IxID INT -- Loop through all indexes OPEN IndexCursor FETCH NEXT FROM IndexCursor INTO @IxTable , @IxTableID , @IxName , @IxID WHILE ( @@FETCH_STATUS = 0 ) BEGIN    DECLARE @IXSQL NVARCHAR ( 4000 )    SET @IXSQL = 'CREATE '    -- Check if the index is unique    IF ( INDEXPROPERTY ( @IxTableID , @IxName , 'IsUnique' ) = 1 )    ...