Daily micro-tips for C#, SQL, performance, and scalable backend engineering.
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.
EXEC sp_fkeys ‘TableName’ You can also specify the schema: EXEC sp_fkeys @pktable_name = ‘TableName’, @pktable_owner = ‘dbo’
–STEP 1: Find Repetitive Records (Only the Active Ones) SELECT INDEX1, INDEX2 INTO #TEMP_TABLE FROM MY_TABLE WHERE ACTIVE = 1 GROUP BY INDEX1, INDEX2 HAVING COUNT(1) > 1 ORDER BY COUNT(1) DESC –STEP 2: Create the Cursor DECLARE @INDEX1 INT DECLARE @INDEX2 INT DECLARE @ID INT DECLARE MY_CURSOR CURSOR FOR SELECT INDEX1, INDEX2 FROM #TEMP_TABLE […]
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 […]
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 […]
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
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 […]
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 […]
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
SELECT CONVERT(DATE, GETDATE())
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 […]
UPDATE x SET x.CODE_DEST = x.New_CODE_DEST FROM ( SELECT CODE_DEST, ROW_NUMBER() OVER (ORDER BY [RS_NOM]) AS New_CODE_DEST FROM DESTINATAIRE_TEMP ) x
The Challenge: You need to calculate the average price, rating, or score in a column. However, the column contains 0 (zero) values that represent “missing data” or “not yet rated.” If you use a standard AVG(), these zeros will pull your average down significantly, giving you an inaccurate result. The Secret: In SQL, the AVG() […]
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, […]
STUFF((SELECT ‘, ‘+ Column1 FROM Table1 FOR XML PATH(”)),1,1,”) AS ConcatenatedColumn StudentName ————- Mary John Sam Alaina Edward Result: Mary, John, Sam, Alaina, Edward
Are you writing a query that selects data from a table with lots of columns? Does the select list we need to have all or almost all of these columns? Remember, that it is possible to drag and drop objects from Object Explorer to the Query Window. By dragging and dropping the “Columns” node under […]
In SQL Server Management Studio, by default you can only Select Top 1000 Rows or Edit Top 200 Rows for a table as shown in the below snippet.
DATEADD(interval, number, date) Parameter Values (interval, number, date) Parameter Description interval Required. The time/date part to return. Can be one of the following values: year, yyyy, yy = Year quarter, qq, q = Quarter month, mm, m = month dayofyear = Day of the year day, dy, y = Day week, ww, wk = Week […]
First, Let’s have the table and its records:
SELECT REPLACE(REPLACE(REPLACE(‘ With this query you select single spaces’,’ ‘,'<>’),’><‘,”),'<>’,’ ‘) OUTPUT: With this query you select single spaces
You can check the example code:
The Challenge: You have a Users table with thousands of email addresses, and you need to generate a report that separates the Username from the Domain Name. Doing this manually is impossible, and using complex RegEx in SQL can be overkill. The Solution: By combining three core SQL functions—SUBSTRING, CHARINDEX, and LEN—you can perform surgical […]
The Challenge: You have a column of full names, product titles, or long descriptions, and you need to grab just the first and last words. Since SQL doesn’t have a native Split() function that returns an array easily, we have to get creative with string positioning. The Solution: We use a combination of CHARINDEX, SUBSTRING, […]
The Strategy: Never trust your data source blindly. While frontend validation is great for UX, Database Constraints are your last line of defense. By adding a CHECK CONSTRAINT, you ensure that no invalid email address ever touches your storage, whether it comes from an API, a bulk import, or a manual script. The Goal: To […]
SELECT UPPER(LEFT(colname,1)) + LOWER(RIGHT(colname, LEN(colname) – 1)) FROM tablename
Add these lines to the beginning of your stored procedure. The statement is never executed, but for some reason it gets the job done. I really don’t know how and why 🙂 IF 1=0 BEGIN SET FMTONLY OFF END
WITH Records AS (SELECT ROW_NUMBER() OVER(ORDER BY ID_of_your_SQLTable) AS ‘ROW’, * FROM yourSQLTable) SELECT * FROM Records WHERE ROW = X –You can change X with the number you wanna get (For Example ROW = 5 will bring the 5th record)
If there is no default value for the column then you can use this query: ALTER TABLE MyTable ADD CONSTRAINT df_MyTable_MyColumn DEFAULT 0 FOR MyColumn
SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE ‘%search_parameter%’
The Problem: In financial databases, you often expect values to have exactly two decimal places (e.g., $10.50). However, due to bulk imports or rounding errors, “hidden” decimals like 10.50001 can creep into your tables. These tiny fractions can cause massive discrepancies in your total sums and reports. The Smart Solution: Instead of slow string parsing, […]