MS SQL Cheat Sheet

My small MS SQL Cheat Sheet of sql commands

Find table by column name:

Exact match on column name:

SELECT 
  * 
FROM 
   sysobjects 
WHERE 
   id IN
    (SELECT id FROM syscolumns WHERE name ='your column name')


Part of column name to search:

SELECT 
  * 
FROM 
   sysobjects 
WHERE 
   id IN
    (SELECT id FROM syscolumns WHERE name LIKE '%your column name%')

MS SQL 2000 transacion

in this version of sql the transaction is just beginning and is not as clear as it is in later versions of sql. To create transaciton we need to use command TRAN. Which later is used as TRANSACTION.

SQL Search for table name in database by part of table name

SELECT
*
FROM
information_schema.tables
WHERE
table_name like '%partOFTableName%'

Select into another table

SELECT Suppliers.Name, Product, Products.UnitPrice
INTO [Mexican Suppliers]
FROM Suppliers

Select rows with repeating value

If you need to get all values that are used more than once in table you can use following query:

  SELECT
    [column],
    count([column]) 'Count'
    FROM
    [TSTT_Subscribers].[dbo].[tblGSM_subscribers]
    GROUP BY [column]
    HAVING COUNT([column]) > 1

Search for text in all stored procedures for specific database

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Searched_Text_InsertHere%'
AND ROUTINE_TYPE='PROCEDURE'

Get all parameters for stored procedure

ALTER procedure [sys].[sp_procedures_rowset]
(
    @procedure_name     sysname,
    @group_number       int = 1,
    @procedure_schema   sysname = null
)
as
    select
        PROCEDURE_CATALOG       = db_name(),
        PROCEDURE_SCHEMA        = schema_name(pro.schema_id),
        PROCEDURE_NAME          = convert(nvarchar(134),pro.name +';'+ ltrim(str(pro.procedure_number,5))),
        PROCEDURE_TYPE          = convert(smallint, 3), -- DB_PT_FUNCTION
        PROCEDURE_DEFINITION    = convert(nvarchar(1),null),
        DESCRIPTION             = convert(nvarchar(1),null),
        DATE_CREATED            = pro.create_date,
        DATE_MODIFIED           = convert(datetime,null)
    from
        sys.spt_all_procedures pro
    where
        (
            (@procedure_schema is null and pro.name = @procedure_name) or
            object_id(quotename(@procedure_schema) + '.' + quotename(@procedure_name)) = pro.object_id
        ) and
        (@group_number is null or pro.procedure_number = @group_number)
    order by 2, 3

What is file location of database

I needed to find out location of the db files (mdf, ldf). I have used following command to get that information:


SELECT name, physical_name AS current_file_location
FROM sys.master_files

Working with cursors

Here is example of using cursor

code:

DECLARE @processingId int =0
DECLARE ExistingRequests_Cursor CURSOR FOR
SELECT primaryKey FROM table
OPEN ExistingRequests_Cursor
FETCH NEXT FROM ExistingRequests_Cursor INTO @processingId  
WHILE @@FETCH_STATUS = 0
BEGIN


 SELECT @processingId
   END  
  
   FETCH NEXT FROM ExistingRequests_Cursor
END
CLOSE ExistingRequests_Cursor
DEALLOCATE ExistingRequests_Cursor

Copy one column of table into another

Memory glitch? Here you are

UPDATE <tablename>
SET <destination column name> = <source column name>

Truncate table

Want to delete table fast? use command:

truncate table replaceWithYourTableName

Display information into console

run command: 

PRINT 'Hello, world!'

  Get All talbes that are for specific database

Each Database have its own sys.objects, so you have to get the databases from sys.databases, and

select from {dbname}.sys.objects

Example : SELECT * FROM HomeSite..sysobjects WHERE [type] = 'U'