create_insert_or_update_statement_from_table_all_rows.sql | |
File Size: | 8 kb |
File Type: | sql |
create_insert_or_update_statement_from_table_all_rows_one_line.sql | |
File Size: | 9 kb |
File Type: | sql |
SubString
SELECT Name ,SUBSTRING(Name, 1, CHARINDEX(' ', Name) - 1) AS [FirstName],
SUBSTRING(Name, CHARINDEX(' ', Name) + 1, LEN(Name)) AS [LastName] FROM dbo.OwnerName
BR_Add
SELECT Name ,SUBSTRING(Name, 1, CHARINDEX(' ', Name) - 1) AS [FirstName],
SUBSTRING(Name, CHARINDEX(' ', Name) + 1, LEN(Name)) AS [LastName] FROM dbo.OwnerName
BR_Add
Identity Reset
SELECT @@IDENTITY from dbo.Pet_Owner_Details
DBCC CHECKIDENT (Pet_Owner_Details, NORESEED)\
DBCC CHECKIDENT (Pet_Details, RESEED, 15423)
SELECT @@IDENTITY from dbo.Pet_Owner_Details
DBCC CHECKIDENT (Pet_Owner_Details, NORESEED)\
DBCC CHECKIDENT (Pet_Details, RESEED, 15423)
1) Search in All Objects
This script search stored procedures, views, functions as well other objects.
-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'EntityID' + '%'
GO
2) Search in Stored Procedure
This script search only stored procedure for specified column.
-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'EntityID' + '%'
GO
3)Kill Active Transactions
SELECT * FROM sys.dm_tran_session_transactions
KILL '50'
4)Search in Table
SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%CheckinLinks%'
5)How to Left Pad a Number with Zeros?
select empid, replicate('0', 6 - len(salary)) + cast (salary as varchar) as salary from emp;
or
SELECT FORMAT (salary, '000000') FROM emp;(sql server 2012)
This script search stored procedures, views, functions as well other objects.
-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'EntityID' + '%'
GO
2) Search in Stored Procedure
This script search only stored procedure for specified column.
-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'EntityID' + '%'
GO
3)Kill Active Transactions
SELECT * FROM sys.dm_tran_session_transactions
KILL '50'
4)Search in Table
SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%CheckinLinks%'
5)How to Left Pad a Number with Zeros?
select empid, replicate('0', 6 - len(salary)) + cast (salary as varchar) as salary from emp;
or
SELECT FORMAT (salary, '000000') FROM emp;(sql server 2012)