Monday 24 October 2016
Dear all these are the query to reduce
size of log file in sql
Query 1
ALTER DATABASE
databasename
SET RECOVERY
SIMPLE;
GO
Query 2
DBCC SHRINKFILE (nameoflogfile, 1)
Query 3
ALTER DATABASE
databasename
SET RECOVERY
FULL;
In this query
Step 1 go to
property of database
Step 2 go to file
option
Step 3 copy file name
of log file
Step 4 replace
that name in above query
And run query in
given sequence
After it check the
size of log file
*** be careful
copy only log file name
Friday 23 September 2016
Monday 22 August 2016
Wednesday 10 August 2016
Tuesday 9 August 2016
Get Foriegn Keys used in Other Tables
Please Pass your table name
To find out all references used in tables
select A.name as ForiegnKeyTable,
B.name as ForeignKeyColumn
from sys.foreign_key_columns as C
inner join sys.tables as A on C.parent_object_id = A.object_id
inner join sys.columns as B on C.parent_object_id = B.object_id and
C.parent_column_id = B.column_id
where C.referenced_object_id = (select object_id from sys.tables
where name = '<Table Name>')
order by ForiegnKeyTable
To find out all references used in tables
select A.name as ForiegnKeyTable,
B.name as ForeignKeyColumn
from sys.foreign_key_columns as C
inner join sys.tables as A on C.parent_object_id = A.object_id
inner join sys.columns as B on C.parent_object_id = B.object_id and
C.parent_column_id = B.column_id
where C.referenced_object_id = (select object_id from sys.tables
where name = '<Table Name>')
order by ForiegnKeyTable
Function for converting Numeric to Word
Step 1 :--
Run this script
CREATE TABLE [dbo].[M_Words](
[Code] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
[WNumber] [int] NOT NULL,
[Wwords] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[M_Words] ON
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(1 AS Numeric(9, 0)), 0, N'Zero')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(2 AS Numeric(9, 0)), 1, N'One')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(3 AS Numeric(9, 0)), 2, N'Two')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(4 AS Numeric(9, 0)), 3, N'Three')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(5 AS Numeric(9, 0)), 4, N'Four')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(6 AS Numeric(9, 0)), 5, N'Five')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(7 AS Numeric(9, 0)), 6, N'Six')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(8 AS Numeric(9, 0)), 7, N'Seven')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(9 AS Numeric(9, 0)), 8, N'Eight')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(10 AS Numeric(9, 0)), 9, N'Nine')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(11 AS Numeric(9, 0)), 10, N'Ten')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(12 AS Numeric(9, 0)), 11, N'Eleven')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(13 AS Numeric(9, 0)), 12, N'Tweleve')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(14 AS Numeric(9, 0)), 13, N'Thirteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(15 AS Numeric(9, 0)), 14, N'Fourteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(16 AS Numeric(9, 0)), 15, N'Fifteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(17 AS Numeric(9, 0)), 16, N'Sixteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(18 AS Numeric(9, 0)), 17, N'Seventeen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(19 AS Numeric(9, 0)), 18, N'Eighteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(20 AS Numeric(9, 0)), 19, N'Ninteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(21 AS Numeric(9, 0)), 20, N'Twenty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(22 AS Numeric(9, 0)), 30, N'Thirty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(23 AS Numeric(9, 0)), 40, N'Fourty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(24 AS Numeric(9, 0)), 50, N'Fifty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(25 AS Numeric(9, 0)), 60, N'Sixty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(26 AS Numeric(9, 0)), 70, N'Seventy')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(27 AS Numeric(9, 0)), 80, N'Eighty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(28 AS Numeric(9, 0)), 90, N'Ninty')
SET IDENTITY_INSERT [dbo].[M_Words] OFF
Step 2
Make This Function
CREATE FUNCTION dbo.[Ravi_NumToWords](@Number Numeric(18,2),@CPaise Char(1))
RETURNS varchar(100) AS
BEGIN
Declare @StrNumber varchar(10), @SLacs char(2), @SThou char(2), @SHun char(2)
Declare @STenUnt char(2), @STen char(2), @SUnt char(2), @SDecimal char(2)
Declare @ILacs Int, @IThou Int, @IHun Int, @ITenUnt Int, @ITen Int, @IUnt Int, @IDecimal Int
Declare @SNumToWords varchar(100), @Wwords varchar(10)
Select @StrNumber = Replicate('0',10-Len(LTrim(RTrim(convert(varchar,@Number))))) + LTrim(RTrim(Convert(varchar,@Number)))
--Print @StrNumber
--Print Len(@StrNumber)
Select @SNumToWords = ''
--Print Len(LTrim(RTrim(convert(varchar,@Number))))
If Len(LTrim(RTrim(convert(varchar,@Number)))) > 4
Begin
--Print Len(@StrNumber)
Select @SLacs = Substring(@StrNumber,1,2)
--Print @SLacs
Select @ILacs = Convert(int,@SLacs)
If @ILacs > 0
Begin
Select @STen = Substring(@StrNumber,1,1)
Select @SUnt = Substring(@StrNumber,2,1)
if Convert(int,@STen) = 1
Begin
Select @ITen = Convert(int,Substring(@StrNumber,1,2))
Select @IUnt = 0
End
Else
Begin
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
End
If @ITen > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
--Select @Wwords = ''
--Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
--Print @Wwords
Select @SNumToWords = @SNumToWords + ' Lakh'
End
Select @SThou = Substring(@StrNumber,3,2)
--Print @SThou
Select @IThou = Convert(int,@SThou)
If @IThou > 0
Begin
Select @STen = Substring(@StrNumber,3,1)
Select @SUnt = Substring(@StrNumber,4,1)
if Convert(int,@STen) = 1
Begin
Select @ITen = Convert(int,Substring(@StrNumber,3,2))
Select @IUnt = 0
End
Else
Begin
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
End
If @ITen > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
--Select @Wwords = ''
--Select @Wwords = Wwords From M_Words Where WNumber = @IThou
--Print @Wwords
--Select @SNumToWords = @SNumToWords + @Wwords + ' Thousand '
Select @SNumToWords = @SNumToWords + ' Thousand '
End
Select @SHun = Substring(@StrNumber,5,1)
--Print @SHun
Select @IHun = Convert(int,@SHun)
If @IHun > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IHun
--Print @Wwords
Select @SNumToWords = @SNumToWords + @Wwords + ' Hundred'
End
Select @STenUnt = Substring(@StrNumber,6,2)
---Print @STenUnt
Select @ITenUnt = Convert(int,@STenUnt)
If @ITenUnt > 0
Begin
Select @STen = Substring(@StrNumber,6,1)
Select @SUnt = Substring(@StrNumber,7,1)
if Convert(int,@STen) = 1
Begin
Select @ITen = Convert(int,Substring(@StrNumber,6,2))
Select @IUnt = 0
End
Else
Begin
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
End
If @ITen > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
End
Select @SNumToWords = @SNumToWords + Space(1) + 'Rupees Only/-' --Only/-
End
Else
Begin
--Print Len(@StrNumber)
--Print LTrim(RTrim(convert(varchar,@Number)))
Select @SLacs = Substring(LTrim(RTrim(convert(varchar,@Number))),1,1)
--Print @SLacs
Select @ILacs = Convert(int,@SLacs)
If @ILacs > 0 and @ILacs <> 1
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords + Space(1) + 'Rupees'
End
Else
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
--Print @Wwords
Select @SNumToWords = @SNumToWords + @Wwords + Space(1) + 'Rupee'
End
End
If @CPaise = 'Y'
Begin
Select @SDecimal = Substring(@StrNumber,9,2)
Select @IDecimal = Convert(int,@SDecimal)
If @IDecimal > 0
Begin
Select @SNumToWords = @SNumToWords + ' and'
Select @STen = Substring(@SDecimal,1,1)
Select @SUnt = Substring(@SDecimal,2,1)
if Convert(int,@STen) = 1
Begin
Select @ITen = Convert(int,Substring(@StrNumber,9,2))
Select @IUnt = 0
End
Else
Begin
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
End
If @ITen > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
Select @SNumToWords = @SNumToWords + Space(1) + 'Paise'
End
End
--Print LTrim(RTrim(@SNumToWords))
Return LTrim(RTrim(@SNumToWords))
End
Step 3
Use Like This
select dbo.[Ravi_NumToWords] (15264,'y')
Note :- This Function's max limit is 1 to 9999999
Shrink Database File
By Using This query we can reduce database file
Firstly we should get File Name by Using This
with TblSpace as
(
select
Name,
size * 8.0 / 1024 as TotalSize,
fileproperty(name, 'SpaceUsed') * 8.0 / 1024 as UsedSpace
from sys.database_files
)
select
Name,
TotalSize,
UsedSpace,
convert(decimal(5, 2), UsedSpace / TotalSize * 100) as UsedSpacePer
from TblSpace
Get File Name and use that file name in this query to reduce
dbcc shrinkfile(<Database_File_Name>, <Target_Size_in_MB>)
Query to Get Database file and Spaces
By using this query we can get all using database files and their space status
with TblSpace as
(
select
Name,
size * 8.0 / 1024 as TotalSize,
fileproperty(name, 'SpaceUsed') * 8.0 / 1024 as UsedSpace
from sys.database_files
)
select
Name,
TotalSize,
UsedSpace,
convert(decimal(5, 2), UsedSpace / TotalSize * 100) as UsedSpacePer
from TblSpace
with TblSpace as
(
select
Name,
size * 8.0 / 1024 as TotalSize,
fileproperty(name, 'SpaceUsed') * 8.0 / 1024 as UsedSpace
from sys.database_files
)
select
Name,
TotalSize,
UsedSpace,
convert(decimal(5, 2), UsedSpace / TotalSize * 100) as UsedSpacePer
from TblSpace
Friday 5 August 2016
Friday 29 July 2016
Saturday 23 July 2016
How to use cursor in SQL Server
Please Use this in your procedures and functions
DECLARE C21 CURSOR FOR <Query to find Value on basis of ...>
OPEN C21
FETCH FROM C21 INTO @col
WHILE @@FETCH_STATUS = 0 BEGIN
-----Do your tasks here according to cursor
FETCH FROM C21 INTO @col
END
CLOSE C21
DEALLOCATE C21
Calculate Age in SQL Server
Please Pass your date of birth in "DD/MM/YYYY" format and get age .....
Create a Procedure...
create Proc dbo.CalculateAge_ravi
(@date1 varchar(15))
as
DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = CONVERT(datetime,@date1,103)
SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
SELECT @years as Years, @months as Months, @days as Days
Get Your Age
exec CalculateAge_ravi 'Pass your DOB'
Wednesday 13 July 2016
Get AlfaNumeric Values From a column of a table in sql server
Function to get Only Numeric Value
Please Use this function for that
CREATE FUNCTION dbo.Ravi_GetNumeric
(@CompeteStr VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @NumericValues INT
SET @NumericValues = PATINDEX('%[^0-9]%', @CompeteStr )
BEGIN
WHILE @NumericValues > 0
BEGIN
SET @CompeteStr = STUFF(@CompeteStr , @intAlpha, 1, '' )
SET @NumericValues = PATINDEX('%[^0-9]%', @CompeteStr )
END
END
RETURN ISNULL(@CompeteStr ,0)
END
Generate Script of a Table in Sql server (Using Query)
Step 1. Please pass Your table Name in this Stored Procedure and get script
Create Proc dbo.Ravi_Script_Table
(@tablename varchar(50))
as
begin
DECLARE
@object_name SYSNAME
, @object_id INT
, @SQL NVARCHAR(MAX)
SELECT
@object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
, @object_id = [object_id]
FROM (SELECT [object_id] = OBJECT_ID(@tablename, 'U')) o
SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT CHAR(13) + ' , [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)
ELSE
CASE WHEN c.system_type_id != c.user_type_id
THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'
ELSE '[' + UPPER(tp.name) + ']'
END +
CASE
WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('nvarchar', 'nchar')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length / 2 AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name = 'decimal'
THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id
THEN ' COLLATE ' + c.collation_name
ELSE ''
END +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END +
CASE WHEN c.default_object_id != 0
THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +
' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)
ELSE ''
END +
CASE WHEN cc.[object_id] IS NOT NULL
THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]
ELSE ''
END +
CASE WHEN c.is_identity = 1
THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +
CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'
ELSE ''
END
END
FROM sys.columns c WITH(NOLOCK)
JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.check_constraints cc WITH(NOLOCK)
ON c.[object_id] = cc.parent_object_id
AND cc.parent_column_id = c.column_id
WHERE c.[object_id] = @object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ') +
ISNULL((SELECT '
, CONSTRAINT [' + i.name + '] PRIMARY KEY ' +
CASE WHEN i.index_id = 1
THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END +' (' + (
SELECT STUFF(CAST((
SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +
CASE WHEN ic.is_descending_key = 1
THEN ' DESC'
ELSE ''
END
FROM sys.index_columns ic WITH(NOLOCK)
WHERE i.[object_id] = ic.[object_id]
AND i.index_id = ic.index_id
FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'
FROM sys.indexes i WITH(NOLOCK)
WHERE i.[object_id] = @object_id
AND i.is_primary_key = 1), '') + CHAR(13) + ');'
PRINT @SQL
end
Create Proc dbo.Ravi_Script_Table
(@tablename varchar(50))
as
begin
DECLARE
@object_name SYSNAME
, @object_id INT
, @SQL NVARCHAR(MAX)
SELECT
@object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
, @object_id = [object_id]
FROM (SELECT [object_id] = OBJECT_ID(@tablename, 'U')) o
SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT CHAR(13) + ' , [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)
ELSE
CASE WHEN c.system_type_id != c.user_type_id
THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'
ELSE '[' + UPPER(tp.name) + ']'
END +
CASE
WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('nvarchar', 'nchar')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length / 2 AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name = 'decimal'
THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id
THEN ' COLLATE ' + c.collation_name
ELSE ''
END +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END +
CASE WHEN c.default_object_id != 0
THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +
' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)
ELSE ''
END +
CASE WHEN cc.[object_id] IS NOT NULL
THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]
ELSE ''
END +
CASE WHEN c.is_identity = 1
THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +
CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'
ELSE ''
END
END
FROM sys.columns c WITH(NOLOCK)
JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.check_constraints cc WITH(NOLOCK)
ON c.[object_id] = cc.parent_object_id
AND cc.parent_column_id = c.column_id
WHERE c.[object_id] = @object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ') +
ISNULL((SELECT '
, CONSTRAINT [' + i.name + '] PRIMARY KEY ' +
CASE WHEN i.index_id = 1
THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END +' (' + (
SELECT STUFF(CAST((
SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +
CASE WHEN ic.is_descending_key = 1
THEN ' DESC'
ELSE ''
END
FROM sys.index_columns ic WITH(NOLOCK)
WHERE i.[object_id] = ic.[object_id]
AND i.index_id = ic.index_id
FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'
FROM sys.indexes i WITH(NOLOCK)
WHERE i.[object_id] = @object_id
AND i.is_primary_key = 1), '') + CHAR(13) + ');'
PRINT @SQL
end
Change DataType or Size of a Column in Sql Server
Step 1
Alter Table <Table Name>Alter Column <Column Name> <New Data Type with Size>
Note : If you find any error of transaction log file the please Run This Query
ALTER DATABASE <DataBase Name>
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (DataBase Name_log, 1);
GO
sp_helpdb <DataBase Name>
GO
ALTER DATABASE <DataBase Name>
SET RECOVERY FULL
GO
Please Use again Step 1
Thursday 7 July 2016
Kill Sleeping Threads in SQL to Remove DeadLock Condition
Use This Process to Kill sleeping Threads in sql
CREATE proc [dbo].[KillProcess]
as
begin
declare @msrno1 varchar(10) ,@spid varchar(110)='',@count int =0,
@mysql varchar(100)=''
DECLARE C21 CURSOR FOR select spid from sysprocesses where status = 'sleeping' and cmd='AWAITING COMMAND' order by spid
OPEN C21
FETCH FROM C21 INTO @msrno1
WHILE @@FETCH_STATUS = 0 BEGIN
set @count=0
set @mysql=''
select @count= count(1) from sysprocesses where spid=@msrno1 and status = 'sleeping' and cmd='AWAITING COMMAND'
if @count>0
begin
set @mysql= 'kill '+ cast(@msrno1 as varchar(10))
exec( @mysql)
end
FETCH FROM C21 INTO @msrno1
END
CLOSE C21
DEALLOCATE C21
Select 'Done'
end
Wednesday 6 July 2016
Monday 4 July 2016
Reset Identity Column in sql server
Here is the query to reset identity column value
DBCC CHECKIDENT( <TableName> , RESEED, <Values>)
<Table Name> :- Table Name in which you want to reset identity column.
<Values> :- This is value by which you want to increment column value
eg. this may 1 (It increment values by one )
DBCC CHECKIDENT( <TableName> , RESEED, <Values>)
<Table Name> :- Table Name in which you want to reset identity column.
<Values> :- This is value by which you want to increment column value
eg. this may 1 (It increment values by one )
Tuesday 28 June 2016
Copy One table data to next table...
Step 1 . If destination table have any identity column then firstly off identity by this query
.... Data Copied
Step 3 Please On its identity Column
SET IDENTITY_INSERT DestinationTable ON
Step 2 Put this query in editor
Insert into db.
DestinationTable
(clomunname1,clomunname2,clomunname3) select clomunname1,clomunname2,clomunname3 fromSourceTable
.... Data Copied
Step 3 Please On its identity Column
SET IDENTITY_INSERT DestinationTable OFF
Saturday 25 June 2016
Sql Solutions: This is the Query to delete duplicate values from ...
Sql Solutions: This is the Query to delete duplicate values from ...: This is the Query to delete duplicate values from a table...... delete FROM <tablename> WHERE <tablename>.%%physloc%% NOT...