This is a system SP for looping a command in all databases in a server.
Question mark is used for DB name.
Following query selects DB name and the number of tables of each DB;
DECLARE @command VARCHAR(1000) SELECT @command = 'USE ? select ''?'', count(1) from sysobjects where xtype = ''U''' --U for tables, V for views EXEC sp_MSforeachdb @command |
Percent sign is used any number & any character with LIKE search. Today I needed to search a text which has a percent sign (%)
The text I want to search: “1980%”
SELECT * FROM MY_TABLE WHERE MY_COLUMN LIKE '%1980[%]%' |
One more thing, underscore sign (_) is used to search any “1″ character with LIKE.
SELECT * FROM MY_TABLE_ WHERE MY_COLUMN LIKE '%MEM_T%' |
Returns:
MEMET
MEMATİ
Indexes are useful when reading data. But when inserting data into a table, it may be useful to disable the indexes and rebuild them again after insert operation completed.
Especially when loading huge data manually or with ETL, disable index on pre-sql and rebuild on post-sql:
----Diable Index ALTER INDEX [IX_Index_Name] ON dbo.TABLE_NAME DISABLE |
----Enable Index ALTER INDEX [IX_Index_Name] ON dbo.TABLE_NAME REBUILD |
List of the dependent objects on a table…
SELECT OBJECT_NAME(sd.object_id) AS ObjName FROM sys.sql_dependencies sd LEFT JOIN sys.objects o ON o.object_id=sd.object_id LEFT JOIN sys.COLUMNS c ON c.object_id=sd.referenced_major_id AND c.column_id=sd.referenced_minor_id WHERE o.TYPE='V' --Remove this to list all dependent objects AND OBJECT_SCHEMA_NAME(sd.referenced_major_id) = 'dbo' AND sd.referenced_major_id=object_id('THE_TABLE') AND c.name='THE_COLUMN' --Comment this row to list dependencies of the table, not only column GROUP BY schema_name(o.schema_id),OBJECT_NAME(sd.object_id) |
The to_char function converts a number or date to a string.
The syntax for to_char function is: to_char( value, [ format_mask ], [ nls_language ] )
| Parameter | Explanation |
|---|---|
| YEAR | Year, spelled out |
| YYYY | 4-digit year |
| YYY YY Y |
Last 3, 2, or 1 digit(s) of year. |
| IYY IY I |
Last 3, 2, or 1 digit(s) of ISO year. |
| IYYY | 4-digit year based on the ISO standard |
| Q | Quarter of year (1, 2, 3, 4; JAN-MAR = 1). |
| MM | Month (01-12; JAN = 01). |
| MON | Abbreviated name of month. |
| MONTH | Name of month, padded with blanks to length of 9 characters. |
| RM | Roman numeral month (I-XII; JAN = I). |
| WW | Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
| W | Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
| IW | Week of year (1-52 or 1-53) based on the ISO standard. |
| D | Day of week (1-7; 1 = Sunday). |
| DAY | Name of day. |
| DD | Day of month (1-31). |
| DDD | Day of year (1-366). |
| DY | Abbreviated name of day. |
| J | Julian day; the number of days since January 1, 4712 BC. |
| HH | Hour of day (1-12). |
| HH12 | Hour of day (1-12). |
| HH24 | Hour of day (0-23). |
| MI | Minute (0-59). |
| SS | Second (0-59). |
| SSSSS | Seconds past midnight (0-86399). |
| FF | Fractional seconds. |
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 |
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 |
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 |
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 |
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:
