Outlook
Send mail: Ctrl+Enter or Alt+S
Flag mail (Follow up or Mark complete): Insert
SQL Server Management Studio
Show/Hide Results/Messages part: Ctrl+R
Close Query Tab: Ctrl+F4 or Click scroll of mouse
Windows
Run: Windows+R
Lock Computer: Windows+L
Open MS Excel: Run -> excel -> Enter
Open MS Word: Run -> winword -> Enter
Change keyboard language: Alt+Shift
Show Desktop: Windows+M
To be added…
Also please share useful common shortcuts with us…
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
Bu küçük SQL fonksiyonunu TC Kimlik No’ların doğru olup olmadığını sorgulamak için kullanabilirsiniz. Kullanım şekli;
IF dbo.FN_TCNO_DOGRUMU(11111111111)=1 PRINT 'DOGRU TC KIMLIK NO' ELSE PRINT 'YANLIS TC KIMLIK NO'
Bu kodları kullanarak rahatlıkla fonksiyonun C# versiyonunu da hazırlayabilirsiniz.
CREATE FUNCTION [dbo].[FN_TCNO_DOGRUMU](@TcNo BIGINT) RETURNS BIT AS BEGIN DECLARE @ATCNO BIGINT DECLARE @BTCNO BIGINT DECLARE @C1 Tinyint DECLARE @C2 Tinyint DECLARE @C3 Tinyint DECLARE @C4 Tinyint DECLARE @C5 Tinyint DECLARE @C6 Tinyint DECLARE @C7 Tinyint DECLARE @C8 Tinyint DECLARE @C9 Tinyint DECLARE @Q1 INT DECLARE @Q2 INT DECLARE @R Bit SET @ATCNO = @TcNo / 100 SET @BTCNO = @TcNo / 100 IF LEN(CONVERT(VARCHAR(19),@TcNo)) = 11 BEGIN SET @C1 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C2 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C3 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C4 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C5 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C6 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C7 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C8 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @C9 = @ATCNO % 10 SET @ATCNO = @ATCNO / 10 SET @Q1 = ((10-((((@C1+@C3+@C5+@C7+@C9)*3)+(@C2+@C4+@C6+@C8)) % 10))%10) SET @Q2 = ((10-(((((@C2+@C4+@C6+@C8)+@Q1)*3)+(@C1+@C3+@C5+@C7+@C9))%10))%10) IF (@BTCNO * 100)+(@Q1 * 10)+@Q2 = @TcNo SET @R = 1 ELSE SET @R = 0 END ELSE SET @R = 0 RETURN @R END
The error message which SQL Server returns is:
Database errors occurred: Microsoft OLE DB Provider for SQL Server: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Database driver error… Function Name : Execute Multiple
Please check the columns of the table which data is trying to be inserted.
eg. executed script is “INSERT INTO MY_TABLE (MY_COLUMN1, MY_COLUMN1, MY_COLUMN3) VALUES BULABULA…” but MY_TABLE does not have the column MY_COLUMN3!
Hope this helps.
Here you are a very useful script to search a string in all data of tables or views.
You can use this script to find out a parameter stored in which tables.
It is also useful to learn which tables are used for some string in complex repositories as Informatica Repository DB.
USE DB_TO_SEARCH DECLARE @KW VARCHAR(100) = 'STRING_TO_SEARCH', @SQL VARCHAR(MAX) = '' SELECT @SQL += 'IF EXISTS(SELECT * FROM ' + SCHEMA_NAME(t.schema_id) + '.' + T.name + ' WHERE CONVERT(VARCHAR(MAX), ' + c.name + ') LIKE ''%' + @KW + '%'')' + CHAR(10) + 'PRINT ''TABLE NAME:' + SCHEMA_NAME(T.schema_id) + '.' + t.name + '~COLUMN NAME:' + c.name + '''' + CHAR(10) FROM sys.COLUMNS c INNER JOIN sys.TABLES t --Use "sys.views" to seach views instead of tables ON c.object_id = t.object_id INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c.user_type_id = ty.user_type_id WHERE ty.name LIKE '%char%' OR ty.name LIKE '%text%' --You can also change types of columns to search EXEC(@SQL)
The output of the script as;
TABLE NAME:dbo.OPB_DTL_SWIDG_LOG~COLUMN NAME:LOCATION_NAME
TABLE NAME:dbo.OPB_SWIDGET_ATTR~COLUMN NAME:ATTR_VALUE
Please check the article SQL Script to search objects in all databases to search a string in objects, not data.
A very simple SQL Select script to search any _TEXT in stored procedures or functions which belongs to given _DB.
SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION FROM _DB.INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%_TEXT%' AND ROUTINE_TYPE='PROCEDURE' --OR 'FUNCTION'
Here you are another simple and very useful SQL script to search any object in all databases on the server.
Script searches all tables, views, stored procedures and functions of all databases.
SET NOCOUNT ON DECLARE @DB VARCHAR(40), @CMD VARCHAR(1000), @OBJECT_NAME VARCHAR(100) SET @OBJECT_NAME = 'MIS_MASRKOM_PARAM' DECLARE DBNAME CURSOR FOR SELECT NAME FROM SYS.DATABASES ORDER BY 1 OPEN DBNAME FETCH NEXT FROM DBNAME INTO @DB WHILE @@FETCH_STATUS = 0 BEGIN IF @DB NOT IN ('MASTER', 'MSDB', 'MODEL', 'TEMPDB', 'DECISION_MODULE', 'PUBS', 'NORTHWIND') BEGIN SELECT @CMD = 'USE [' + @DB + '] SELECT DISTINCT ''' + RTRIM(@DB) + ''' AS ' + RTRIM(@DB) + ', SO.NAME, SO.TYPE FROM SYS.SYSOBJECTS SO (NOLOCK) JOIN SYS.SYSCOMMENTS SC (NOLOCK) ON SC.ID=SO.ID WHERE SC.TEXT LIKE ''%' + @OBJECT_NAME + '%'' ORDER BY 2, 3' PRINT RTRIM(@CMD) EXEC(@CMD) END FETCH NEXT FROM DBNAME INTO @DB END DEALLOCATE DBNAME
Here is a very useful SQL script to search a column in the given database and tables. It will be one of the most valuable script, if you work on many tables.
-- XTYPE (COLUMN TYPES) -- 52 -> SMALLINT -- 56 -> INT -- 48 -> TINYINT -- 127 -> BIGINT -- 106 -> DECIMAL -- 108 -> NUMERIC -- 175 -> CHAR -- 167 -> VARCHAR -- 58 -> SMALLDATETIME -- 61 -> DATETIME SET NOCOUNT ON DECLARE @DB VARCHAR(100), @DB_NAME VARCHAR(100), @CMD VARCHAR(8000), @COLUMN_NAME VARCHAR(500), @TABLE_NAME VARCHAR(500), @COLUMN_TYPE sysname SET @COLUMN_NAME = '%COLUMN_NAME_HERE%' --THE COLUMN YOU WANT TO SEARCH SET @TABLE_NAME = '%TABLE_NAME_HERE%' --LEAVE BLANK TO SEARCH IN ALL TABLES SET @DB_NAME = 'DB_NAME_HERE' SET @COLUMN_TYPE = 'COLUMN_TYPE_HERE' --PLEASE CHECK COLUMN TYPES ABOVE DECLARE DBNAME CURSOR FOR SELECT name FROM sys.DATABASES WHERE database_id > 4 AND name = @DB_NAME ORDER BY 1 OPEN DBNAME FETCH NEXT FROM DBNAME INTO @DB WHILE @@FETCH_STATUS = 0 BEGIN SET @CMD = 'USE [' + @DB + '] SELECT ''' + @DB + ''' AS DB_ADI, t.name AS TABLO_ADI, c.name AS KOLON_ADI, ty.name, c.system_type_id, c.max_length, c.precision, c.collation_name FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id JOIN sys.types ty ON ty.system_type_id = c.system_type_id WHERE t.type = ''U'' AND c.name LIKE ''' + @COLUMN_NAME + ''' AND T.name LIKE ''' + @TABLE_NAME + ''' ORDER BY 2, 3' EXEC(@CMD) FETCH NEXT FROM DBNAME INTO @DB END CLOSE DBNAME DEALLOCATE DBNAME
This SP is useful where paging is required on huge tables. It selects the exact data instead of getting whole table data.
It works on one table, so that if you have to select from multiple tables, then you can create a view and pass it’s name to this SP as @table parameter. Get the records of @page, from @table, which are order by @orderby columns, @perpage number of records for page.
Following example gets the 23. page from “Customers” table and 50 lines per page. Also records are ordered by “Name” and “Surname” column.
@TABLE = 'Customers' @orderby = 'Name, Surname' @perpage = 50 @page = 23 -- ============================================= -- Author: mSu -- Create date: 20100106 -- Description: get @perpage records of the page @page from the table @table order by @orderby -- exec get_page 'customer','ID',20,17 -- ============================================= ALTER PROC [dbo].[get_page] @TABLE AS VARCHAR(64), @orderby AS VARCHAR(128), @perpage AS INT, @page AS INT AS DECLARE @START AS INT DECLARE @END AS INT SET @START = (@page-1) * @perpage + 1 SET @END = @perpage * @page EXEC ('SELECT TOP (' + @perpage + ') * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @orderby + ') AS ROWNUM,* FROM ' + @TABLE + ' (NOLOCK)) TMP WHERE ROWNUM BETWEEN ' + @START + ' AND ' + @END + ' ORDER BY ROWNUM')
