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 cursorcode:
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'
No comments:
Post a Comment