Powered by Passion

To get all script from database as a create new index into another database you can use the following --Get all Index Script SELECT ' CREATE ' + CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END + I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' + I.name + ' ON ' + Schema_name(T.Schema_id)+'.'+T.name + ' ( ' + KeyColumns + ' ) ' + ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') + ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' + CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' + 'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' + -- default value 'SORT_IN_TEMPDB = OFF ' + ',' + CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ...

If yo want to get all drop index script from database you can use this. like '%idx_%'   = index prefix --Drop All Index declare @qry nvarchar(max); select @qry = (SELECT 'DROP INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ID) + '; ' FROM sysindexes ix WHERE ix.Name IS NOT null and ix.Name like '%idx_%' for xml path('')); SELECT @qry This will return all drop index script on result. now copy the script for your use.

create table #SuspectColumns ( TABLE_SCHEMA sysname, TABLE_NAME sysname, COLUMN_NAME sysname ) declare csrColumns cursor fast_forward for select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE = 'YES' declare @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAME sysname, @sql nvarchar(max) open csrColumns while (1=1) begin fetch next from csrColumns into @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME if @@FETCH_STATUS<>0 break set @sql = N'if not exists(select 1 from ' + QUOTENAME(@TABLE_SCHEMA) + N'.' + QUOTENAME(@TABLE_NAME) + N' where ' + QUOTENAME(@COLUMN_NAME) + N'is not null) insert into #SuspectColumns values (''' + @TABLE_SCHEMA + N''',''' + @TABLE_NAME + N''',''' + @COLUMN_NAME + N''')' exec sp_executes...

i want to check  if any customer has more than 1 same insplanid_FK. ;with cte as ( SELECT *, count(cusid_FK) OVER (partition by cusid_FK,ccardholdername,insplanid_FK) as V_COUNT FROM cusinsplan WHERE ISNULL(insplanid_FK,'')!='' ) select * from cte where V_COUNT>1

If we want to write log file of Entity Framework query in EF 6 we can follow this step Step 1: Open EDMX cs file and copy the code: protected override void OnModelCreating(DbModelBuilder modelBuilder) { Database.Log = (query) => Debug.Write(query); } Step 2 : write log on your Entity Framework query refauthrequests refauthEntity = EM_Refauthrequests.ConvertToEntity(vsspoAuditModel); _db.refauthrequests.Add(refauthEntity); _db.Database.Log = queryLog => { Debug.Print(queryLog); // Debug.Print will show the output on visual studio output window You can write log using queryLog value }; _db.SaveChanges(); If you set debugger you will see the output window

WITH Dates AS ( SELECT [Date] = CONVERT(DATETIME,'1-1-2017') UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date]) FROM Dates WHERE Date < '12-31-2017' ) SELECT [Date] FROM Dates OPTION (MAXRECURSION 1000)

Run all SQL files from  a folder you can follow the process that will save your time for executing SQL script Copy this code in a notepad file and change the Server, Database, Username And Password value as your self and save it as a .bat file. copy the file in your SQL script folder and run this bat file. REM REM development environment only!! REM pause for %%G in (*.sql) do sqlcmd /S "192.168.10.139\SQLEXPRESS" /d "VSSPORT_DEV" -U "atiour" -P "atiour" -i"%%G" pause REM REM All Script Run Successfully REM

I have a string like this: 000014000608,000014000609,000014000610,000014000611 From this string i want to  remove 00001 with '' because this is a prefix of my every coma separated value. REPLACE((SUBSTRING(m_rx_nos,6,LEN(CAST(m_rx_nos AS VARCHAR(500)))-6)),(',0000'+ CONVERT(VARCHAR(100),f.pharminfoid_FK)),',') as m_rx_nos  Out Put: 4000608,4000609,4000610,400061

If you want to get all table name with table primary key coloumn you can use the sql query. SELECT i.name AS IndexName, OBJECT_NAME(ic.OBJECT_ID) AS TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id WHERE i.is_primary_key = 1

Recently i have completed  online training program on T-SQL from Edx. I just share in my blog that will cover all basic T-SQL. -- Display all columns for all customers SELECT * FROM SalesLT.Customer; -- Display customer name fields SELECT Title, FirstName, MiddleName, LastName, Suffix FROM SalesLT.Customer; -- Display title and last name with phone number SELECT Salesperson, Title + ' ' + LastName AS CustomerName, Phone FROM SalesLT.Customer; -- Customer Companies SELECT CAST(CustomerID AS varchar) + ': ' + CompanyName AS CustomerCompany FROM SalesLT.Customer; --Sales Order Revisions SELECT SalesOrderNumber + ' (' + STR(RevisionNumber, 1) + ')' AS OrderRevision, CONVERT(nvarchar(30), OrderDate, 102) AS OrderDate FROM SalesLT.SalesOrderHeader; -- Get middle names if known SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '')+ LastName AS CustomerName FROM Sales...

You resolve your auto increment  jumping from this http://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database you can also resolve the issue into another way if you are using EDMX. Before inserting data into table get the max id value from your table. int maxAge = context.Persons.Max(p => p.Age); Now add your increment number  maxAge+1   Map the id value with your table. NB: You table Identity specification : Is Identity Will be no.

sqlcmd -S ATIK-PC\SQLEXPRESS -d STUDENT_DEV -U sa -P sa123 -i c:\schema.sql -o c:\log.txt

SQL split a comma separated string DECLARE @valueList varchar(8000) DECLARE @pos INT DECLARE @len INT DECLARE @value varchar(8000) DECLARE @i INT=0 SET @valueList = ',Atik,Khabir,Shahed,Shain,Ashek,Noman' set @pos = 0 set @len = 0 WHILE CHARINDEX(',', @valueList, @pos+1)>0 BEGIN set @len = CHARINDEX(',', @valueList, @pos+1) - @pos set @value = SUBSTRING(@valueList, @pos, @len) set @i=@i+1 --SELECT @pos, @len, @value /*this is here for debugging*/ IF(@i=1) -- now you can set condition BEGIN PRINT @value END IF(@i=2) BEGIN PRINT @value END IF(@i=3) BEGIN PRINT @value END IF(@i=4) BEGIN PRINT @value END set @pos = CHARINDEX(',', @valueList, @pos+@len) +1 END

SELECT * FROM (SELECT CAST(mlblmsg AS varchar(max)) AS mlblmsg,clblcode FROM warninglabels) t PIVOT (MAX(mlblmsg) FOR clblcode IN ([0001],[0002],[003],[0004],[0005],[0006]))p

To get the table where this column use you can use this query. SELECT * FROM sys.tables WHERE tables.object_id IN (SELECT OBJECT_ID FROM sys.columns WHERE columns.name LIKE '%CDRUGNAME%') -- use your column name

ALTER PROCEDURE [dbo].[USP_GetPickerNameList] @SearchStr varchar(50) AS BEGIN SET @SearchStr = RTRIM(@SearchStr) + '%' SELECT DISTINCT TOP(10) p.cpickerid ,p.cpickername ,p.cpickeridtype ,p.cid ,p.cidissuingstate ,p.pickerid_PK ,p.pickeridtype_FK ,p.idissuingstate_FK ,c.ccusname ,c.cusid_PK ,c.dbirthday ,f.caddress1 FROM pickers p INNER JOIN pickergroups ON p.pickerid_PK = pickergroups.pickerid_FK INNER JOIN customer c ON c.pickergroupid_FK = pickergroups.pickergroupid_PK LEFT OUTER JOIN family f on c.familyID_FK = f.familyID_PK LEFT OUTER JOIN rx r on c.cusId_PK = r.cusId_FK WHERE RTRIM(LTRIM(p.cpickername)) LIKE @SearchStr OR RTRIM(LTRIM(c.ccusname)) LIKE @SearchStr OR RTRIM(LTRIM(c.ccusfirstname)) LIKE @SearchStr OR RTRIM(LTRIM(c.ccuslastname)) LIKE @SearchStr ...

To get the value of month first date & last date int value you can try this: DECLARE @startOfMonth DATETIME , @MonthID int, @YearId int DECLARE @endOfMonth DATETIME DECLARE @FromDate DateTime,@ToDate DateTime SET @MonthID=2 set @YearId=2014 Select @startOfMonth = CAST(@YearID AS varchar) + '/' + CAST(@MonthID AS varchar) +'/'+ '01' Select @endOfMonth = CAST(@YearID AS varchar) + '/' + CAST(@MonthID AS varchar) + '/'+ CAST(DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@startOfMonth),0))) AS varchar) Set @FromDate=@startOfMonth Set @ToDate=@endOfMonth SELECT @FromDate 'MonthStartDate',@ToDate 'MonthEndDate'

Query: SELECT COUNT(CASE WHEN DayStatusID = 1 THEN 1 END) AS A, COUNT(CASE WHEN DayStatusID = 7 THEN 1 END) AS B, COUNT(CASE WHEN DayStatusID = 3 THEN 1 END) AS C from Payroll_tblYearMonthDayEntry Result:

just set you datefield in Payroll_tblAttendance.InTime Finally: (SELECT RIGHT(LTRIM(RIGHT(CONVERT(varchar, Payroll_tblAttendance.InTime, 100),8)),8)) InTime,

Simply just follow the link http://blog.sqlauthority.com/2007/02/01/sql-server-udf-function-to-convert-text-string-to-title-case-proper-case/#comment-945335