RSS
 

Archive for the ‘SQL Server’ Category

SQL Server “SELECT * FROM” Keyboard Shortcut

20 Oct

Think that how many times you write “SELECT * FROM” a day.

You can select data from a table with one key Ctrl+5

Let’s create an SP under master DB. If this SP is under master DB, we can use shortcut property in all DBs.

USE master
 
CREATE proc sp_select
@TABLE_NAME VARCHAR(100),
@COUNT VARCHAR(10) = NULL
AS
BEGIN
 IF EXISTS (SELECT NULL FROM sys.objects WHERE TYPE IN ('U','V') AND name=@TABLE_NAME)
 BEGIN
  IF @COUNT IS NULL
   SET @COUNT=1000
  EXEC('select top ' + @COUNT + ' * from ' + @TABLE_NAME + '(nolock)')
 END
 ELSE
  print 'Table not found.'
END
GO

bbb

SP is created, now we have to assign shortcut to this Stored Procedure; Tools > Options > Environment > Keyboard

We assigned “Ctrl+5” to sp_select as seen in the picture above.

Now, just write table or view name, select the text and hit Ctrl+5.

MY_TABLE_NAME --Selects TOP 1000

or

MY_TABLE_NAME, 50 --Selects TOP 50
 
No Comments

Posted in English, SQL Server

 

Select Tables’ Size (MB) of a Database Ordered by Descending

12 Sep

This little script lists all tables’ size of a database. Notice that TotalSize results are in MB.

SELECT OBJECT_NAME(ps.object_id) AS ObjectName,
SUM(ps.reserved_page_count) * 8 /1024 AS TotalSize FROM 
sys.dm_db_partition_stats ps JOIN sys.TABLES t
ON ps.object_id = t.object_id
--You can find out some specific tables:
--AND OBJECT_NAME(ps.object_id) LIKE '%TABLE_NAME%'
GROUP BY ps.object_id
ORDER BY 2 DESC
 
No Comments

Posted in English, SQL Server

 

String search in views’ script

06 Sep

Search all views’ script with a given text in a specific schema.

USE DB_TO_SEARCH
 
SELECT SS.name AS [SCHEMA_NAME],
	SO.NAME AS [VIEW_NAME],
	SC.TEXT AS [VIEW_SCRIPT] FROM SYSCOMMENTS SC (NOLOCK)
INNER JOIN SYSOBJECTS SO (NOLOCK) ON SO.ID = SC.ID
INNER JOIN sys.views SV (NOLOCK) ON SO.ID = SV.object_id
INNER JOIN sys.schemas SS (NOLOCK) ON SV.schema_id = SS.schema_id
WHERE SC.TEXT LIKE '%TEXT_2_SEARCH%'
	AND SS.name='SCHEMA_2_SEARCH' --Remove this line to search all schemas
 
No Comments

Posted in English, SQL Server

 

List Dependent Objects of an Object

23 Aug

You can view dependent objects of an object by right clicking as shown below;

You can also list the objects with the following easy script;

SELECT referencing_class AS Class,
	re.referencing_class_desc AS ClassDesc,
	o.type_desc AS ObjectType,
	re.referencing_schema_name AS SchemaName,
	re.referencing_entity_name AS ObjectName,
	re.referencing_id AS ObjectID
FROM sys.dm_sql_referencing_entities ('dbo.DW_HESAP', 'Object') re
LEFT JOIN sys.objects o ON o.object_id = re.referencing_id
WHERE o.type_desc = 'VIEW' --SQL_STORED_PROCEDURE, CHECK_CONSTRAINT, etc
 
No Comments

Posted in English, SQL Server

 

Cross Apply: select detail data in one cell

16 Aug

Very genious MS SQL function; CROSS APPLY.

As you will see below, this function merges all detail data into one cell with single script. Alternative way is using Cursor.

Let’s do it with an example;

First create and fill the main and detail tables, teachers and students

CREATE TABLE #TEACHERS (
 [ID] INT NOT NULL,
 [NAME] VARCHAR(100),
 [ACTIVE] BIT
)
 
CREATE TABLE #STUDENTS (
 [ID] INT NOT NULL,
 [NAME] VARCHAR(100),
 [TEACHER_ID] INT,
 [ACTIVE] BIT
)
 
INSERT INTO #TEACHERS SELECT 1, 'AHMET OZTURK', 1
INSERT INTO #TEACHERS SELECT 2, 'VELI SENTURK', 1
INSERT INTO #TEACHERS SELECT 3, 'MEHMET KOCATURK', 1
INSERT INTO #TEACHERS SELECT 4, 'ALI KAYATURK', 1
INSERT INTO #TEACHERS SELECT 5, 'SERKAN BOSTURK', 1
 
INSERT INTO #STUDENTS SELECT 1, 'SEMA', 1, 1
INSERT INTO #STUDENTS SELECT 2, 'AYLIN', 1, 1
INSERT INTO #STUDENTS SELECT 3, 'MUSTAFA', 1, 1
INSERT INTO #STUDENTS SELECT 4, 'BURCU', 1, 1
INSERT INTO #STUDENTS SELECT 5, 'FATIH', 2, 1
INSERT INTO #STUDENTS SELECT 6, 'TURGAY', 2, 1
INSERT INTO #STUDENTS SELECT 7, 'KADIR', 2, 1
INSERT INTO #STUDENTS SELECT 8, 'MEHTAP', 3, 1
INSERT INTO #STUDENTS SELECT 9, 'IBRAHIM', 3, 1
INSERT INTO #STUDENTS SELECT 10, 'SONER', 3, 1
INSERT INTO #STUDENTS SELECT 11, 'UGUR', 3, 1
INSERT INTO #STUDENTS SELECT 12, 'CEMIL', 3, 1
INSERT INTO #STUDENTS SELECT 13, 'MURAT', 3, 1
INSERT INTO #STUDENTS SELECT 14, 'SAVAS', 4, 1
INSERT INTO #STUDENTS SELECT 15, 'HAKAN', 4, 1
INSERT INTO #STUDENTS SELECT 16, 'DAVUT', 4, 1



 We created the tables and inserted data. Now we can run the magic script :)

SELECT MAIN_T.NAME AS TEACHER_NAME,
ISNULL(LEFT(DETAIL_T.NAME,LEN(DETAIL_T.NAME)-1),'NO STUDENTS!') AS STUDENTS
FROM #TEACHERS MAIN_T
CROSS APPLY (SELECT NAME + ', ' AS [text()]
             FROM #STUDENTS
             WHERE TEACHER_ID=MAIN_T.ID AND ACTIVE=1 ORDER BY NAME
             FOR XML PATH('')) DETAIL_T (NAME)
WHERE MAIN_T.ACTIVE=1
ORDER BY 1

And the result is:

 
No Comments

Posted in English, SQL Server

 

How to change database collation

08 May

To change a running SQL Server DB’s collation, Restrict Access property of the database must be SINGLE_USER.

When you run the following script;

ALTER DATABASE my_DB COLLATE Turkish_CI_AS

You will get the error; The database could not be exclusively locked to perform the operation.

In order to change the collation of database without this error, you can use the script below;

ALTER DATABASE my_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE my_DB COLLATE Turkish_CI_AS
ALTER DATABASE my_DB SET MULTI_USER
 
No Comments

Posted in English, SQL Server

 

How to add Identity Specification to an existing column

03 May

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

If you get this error message when trying to add or drop identity specification to an existing column of a table, you have to disable “Prevent saving changes that require table recreation”

Microsoft SQL Server Management Studio > Tools > Options >

Or alternatively, you can do this with T-SQL using the following script;

/* To prevent any potential data loss issues, */
/* you should review this script in detail before running it */
/* outside the context of the database designer. */
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_deneme_mehmet
(
id BIGINT NOT NULL IDENTITY (1, 1),
name NCHAR(10) NULL --COLUMNS HERE!
)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_deneme_mehmet SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_deneme_mehmet ON
GO
IF EXISTS(SELECT * FROM dbo.deneme_mehmet)
EXEC('INSERT INTO dbo.Tmp_deneme_mehmet
SELECT * FROM dbo.deneme_mehmet WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_deneme_mehmet OFF
GO
DROP TABLE dbo.deneme_mehmet
GO
EXECUTE sp_rename N'dbo.Tmp_deneme_mehmet', N'deneme_mehmet', 'OBJECT'
GO
COMMIT

Please replace “deneme_mehmet” with the table name and “id” with the column name which to add identity property.

 
No Comments

Posted in English, SQL Server

 

SQL Delete Operation using While Loop

28 Apr 
DECLARE @EACH_UPDATE_COUNT INT
 
SET @EACH_UPDATE_COUNT=1000 --YOU CAN CHANGE THIS VALUE
 
SET ROWCOUNT @EACH_UPDATE_COUNT
 
WHILE 1=1
      BEGIN
            DELETE tmp
            IF @@ROWCOUNT = 0 BREAK
      END
 
No Comments

Posted in English, SQL Server

 

SQL Update Operation using While Loop

07 Apr

For faster update of tables which have huge data, we can use a while loop as given below;

DECLARE @EACH_UPDATE_COUNT INT
DECLARE @LOOP_COUNT BIGINT
 
SET @EACH_UPDATE_COUNT=1000 --YOU CAN CHANGE THIS VALUE
SET @LOOP_COUNT=0 --DO NOT CHANGE
 
WHILE 1=1
BEGIN
WITH tmp AS (
SELECT TOP (@EACH_UPDATE_COUNT) * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY ID_COLUMN ASC) AS ROWID,* FROM MY_TABLE) T
WHERE ROWID > @LOOP_COUNT*@EACH_UPDATE_COUNT AND ROWID <= (@LOOP_COUNT+1)*@EACH_UPDATE_COUNT
)
UPDATE tmp SET UPDATE_COLUMN = 'NEW_VALUE' --OR YOU CAN ALSO MAKE A DELETE OPERATION HERE
--DELETE tmp
IF @@ROWCOUNT = 0 BREAK
SET @LOOP_COUNT=@LOOP_COUNT+1
END
 
No Comments

Posted in English, SQL Server

 

Vergi Kimlik No Doğrulama SQL Fonksiyonu

01 Apr

Bu küçük SQL fonksiyonunu Vergi Kimlik No’ların doğru olup olmadığını sorgulamak için kullanabilirsiniz. Kullanım şekli;

IF dbo.FN_VKNO_DOGRUMU(1111111111)=1
	PRINT 'DOGRU VERGI KIMLIK NO'
ELSE
	PRINT 'YANLIS VERGI KIMLIK NO'

Bu kodları kullanarak rahatlıkla fonksiyonun C# versiyonunu da hazırlayabilirsiniz.

CREATE FUNCTION [dbo].[FN_VKNO_DOGRUMU](@VKNO VARCHAR(10))
RETURNS BIT
BEGIN
DECLARE
@V_LAST_DIGIT SMALLINT,
@V_1 SMALLINT,
@V_2 SMALLINT,
@V_3 SMALLINT,
@V_4 SMALLINT,
@V_5 SMALLINT,
@V_6 SMALLINT,
@V_7 SMALLINT,
@V_8 SMALLINT,
@V_9 SMALLINT,
@V_11 SMALLINT,
@V_22 SMALLINT,
@V_33 SMALLINT,
@V_44 SMALLINT,
@V_55 SMALLINT,
@V_66 SMALLINT,
@V_77 SMALLINT,
@V_88 SMALLINT,
@V_99 SMALLINT,
@TOPLAM SMALLINT,
@R Bit
--
SET @V_1 = (CAST(SUBSTRING(@VKNO,1,1) AS SMALLINT) + 9) % 10
SET @V_2 = (CAST(SUBSTRING(@VKNO,2,1) AS SMALLINT) + 8) % 10
SET @V_3 = (CAST(SUBSTRING(@VKNO,3,1) AS SMALLINT) + 7) % 10
SET @V_4 = (CAST(SUBSTRING(@VKNO,4,1) AS SMALLINT) + 6) % 10
SET @V_5 = (CAST(SUBSTRING(@VKNO,5,1) AS SMALLINT) + 5) % 10
SET @V_6 = (CAST(SUBSTRING(@VKNO,6,1) AS SMALLINT) + 4) % 10
SET @V_7 = (CAST(SUBSTRING(@VKNO,7,1) AS SMALLINT) + 3) % 10
SET @V_8 = (CAST(SUBSTRING(@VKNO,8,1) AS SMALLINT) + 2) % 10
SET @V_9 = (CAST(SUBSTRING(@VKNO,9,1) AS SMALLINT) + 1) % 10
SET @V_LAST_DIGIT = CAST(SUBSTRING(@VKNO,10,1) AS SMALLINT)
--
SET @V_11 = (@V_1 * 512) % 9
SET @V_22 = (@V_2 * 256) % 9
SET @V_33 = (@V_3 * 128) % 9
SET @V_44 = (@V_4 * 64) % 9
SET @V_55 = (@V_5 * 32) % 9
SET @V_66 = (@V_6 * 16) % 9
SET @V_77 = (@V_7 * 8) % 9
SET @V_88 = (@V_8 * 4) % 9
SET @V_99 = (@V_9 * 2) % 9
--
IF @V_1 != 0 AND @V_11 = 0 SET @V_11 = 9
IF @V_2 != 0 AND @V_22 = 0 SET @V_22 = 9
IF @V_3 != 0 AND @V_33 = 0 SET @V_33 = 9
IF @V_4 != 0 AND @V_44 = 0 SET @V_44 = 9
IF @V_5 != 0 AND @V_55 = 0 SET @V_55 = 9
IF @V_6 != 0 AND @V_66 = 0 SET @V_66 = 9
IF @V_7 != 0 AND @V_77 = 0 SET @V_77 = 9
IF @V_8 != 0 AND @V_88 = 0 SET @V_88 = 9
IF @V_9 != 0 AND @V_99 = 0 SET @V_99 = 9
--
SET @TOPLAM = @V_11 + @V_22 + @V_33 + @V_44 +  @V_55 + @V_66 +  @V_77 + @V_88 + @V_99
IF @TOPLAM %10 =0
BEGIN
SET @TOPLAM=0
END
ELSE
BEGIN
SET @TOPLAM = (10 - (@TOPLAM %10))
END
--
IF @TOPLAM = @V_LAST_DIGIT
SET @R = 1 -- DOĞRU
ELSE
SET @R = 0 -- YANLIŞ
--
RETURN @R
--
END
 
No Comments

Posted in SQL Server, Türkçe

 
« Older Entries