DBCC CHECKIDENT(‘TableName’, RESEED, 0) Note that after running DBCC CHECKIDENT(‘TableName’, RESEED, 0): – Newly created tables will start with identity 0 – Existing tables will continue with identity 1
Tag: sql
Unique Constraints in MS SQL Server to prevent duplications
The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint […]
View the Definition of a Stored Procedure in SQL
– In Object Explorer, connect to an instance of the Database Engine. – On the toolbar, click New Query. – In the query window, enter the following statement that uses the sp_helptext system stored procedure. Change the database name and stored procedure name to reference the database and stored procedure that you want. EXEC sp_helptext […]
SQL ‘string_split()’ function
string_split() is a table-valued function that splits a string into rows of substrings, based on a specified separator character. SELECT * FROM string_split(’21;32;34;47;53;61;’, ‘;’) OUTPUT ====== 21 32 34 47 53 61
How to use Replicate function for masking in SQL?
You can use SQL “Replicate” command to mask the data. Below code shows how to make it:
Rename column SQL Server
EXEC sp_RENAME ‘TableName.OldColumnName’ , ‘NewColumnName’, ‘COLUMN’
sp_help for SQL Server Management Studio
If you have SQL Server Management Studio, open it up and execute sp_help ‘dbo.TableName‘. See which column that FK is on, and which column of which table it references. And also that code will let you see many details about Target Table. You can use the same with shourtcut ALT + F1 as well.
List all Foreign Keys referencing a given table in SQL Server
EXEC sp_fkeys ‘TableName’ You can also specify the schema: EXEC sp_fkeys @pktable_name = ‘TableName’, @pktable_owner = ‘dbo’
Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
These 2 scripts clean all views, SPS, functions PKs, FKs and tables. 1. The First script by Adam Anderson, updated to support objects in other schemas than dbo: — check constraints select @stmt = isnull( @stmt + @n, ” ) + ‘alter table [‘ + schema_name(schema_id) + ‘].[‘ + object_name( parent_object_id ) + ‘] drop […]
How to Alter a Computed Column in SQL?
There is NO way to alter computed column. You have to drop and recreate it. Let’s say you have a table with 3 column. And the 3rd one is the sum of the other two: CREATE TABLE MyTable (Column1 int, Column2 int, Column3 AS Column1 + Column2); If you want to change that computed column as multiply of the other […]
How to solve “A cursor with the name already exists” problem?
If you get this error then it means you are using global cursor that will be defined each time you are calling this procedure and give you the same error. Define a local cursor. Just put the keyword LOCAL after CURSOR: DECLARE MyCursor CURSOR LOCAL FOR … Reference
Create, Alter, Drop and Execute SQL Server Stored Procedures
A stored procedure is a saved block of T-SQL code, such as a query to list the rows in a table. A block of T-SQL code can be saved in a T-SQL script file. You can also store the code from a script file in a stored procedure. There are several benefits that result from […]
SQL Query to Find and Replace text in a stored procedures
Declare @spnames CURSOR Declare @spname nvarchar(max) Declare @moddef nvarchar(max) Set @spnames = CURSOR FOR select distinct object_name(c.id) from syscomments c, sysobjects o where c.text like ‘%findtext%’ and c.id = o.id and o.type = ‘P’ OPEN @spnames FETCH NEXT FROM @spnames into @spname WHILE @@FETCH_STATUS = 0 BEGIN Set @moddef = (SELECT Replace ((REPLACE(definition,’findtext’,’replacetext’)),’ALTER’,’create’) FROM sys.sql_modules […]
Return number of rows affected by UPDATE statements in SQL
DECLARE @Teams TABLE( Name varchar(100) ) INSERT INTO @Teams VALUES (N’GALATASARAY’) INSERT INTO @Teams VALUES (N’LIVERPOOL’) INSERT INTO @Teams VALUES (N’BARCELONA’) INSERT INTO @Teams VALUES (N’PARIS SAINT GERMAIN’) INSERT INTO @Teams VALUES (N’JUVENTUS’) UPDATE @Teams SET Name = N’PORTO’ WHERE Name = ‘PARIS SAINT GERMAIN’ SELECT @@ROWCOUNT –gives 1
How to return only the Date part from SQL Server DateTime datatype
SELECT CONVERT(DATE, GETDATE())
“ORDER BY” with CASE and UNION in SQL Server
If you want to use the combination of ORDER BY, CASE and UNION then you should use inner query for a solution without any headache: SELECT * FROM ( SELECT Col_a, Col_b, 0 AS Col_c FROM table1 WHERE conditions UNION SELECT Col_a, NULL AS Col_b, Col_c FROM table2 WHERE conditions ) x ORDER BY CASE […]
Average of all values in a column that are not zero in SQL
SELECT AVG (CASE WHEN Value <> 0 THEN Value ELSE NULL END) …. AVG function will not take into account NULL values. So you can also use this AVG (NULLIF(Value, 0))
Date and Time Conversions in SQL Server
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, […]