procUtils_SqlCheatSheet

alter procedure [procUtils_SqlCheatSheet]

as
set nocount on

--what was the name of the table with something like role
/*
SELECT * from sys.tables where [name] like '%db%'
*/
-- what are the columns of this table
/*
select column_name , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH, table_name from Information_schema.columns where table_name='tbGui_Executedb'
*/

-- find proc
--what was the name of procedure with something like role
/*
select * from sys.procedures where [name] like '%ext%'
exec sp_HelpText procName
*/
/*
exec sp_helpText procUtils_InsertGenerator
*/
--how to list all databases in sql server
/*
SELECT database_id AS ID, NULL AS ParentID, name AS Text FROM sys.databases ORDER BY [name]
*/

--HOW-TO LIST ALL TABLES IN A SQL SERVER 2005 DATABASE
/*
SELECT TABLE_NAME FROM [db].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'dtproperties'
ORDER BY TABLE_NAME


*/
--HOW-TO ENABLE XP_CMDSHELL START
-------------------------------------------------------------------------
-- configure verbose mode temporarily
-- EXECUTE sp_configure 'show advanced options', 1
-- RECONFIGURE WITH OVERRIDE
--GO


--ENABLE xp_cmdshell
-- EXECUTE sp_configure 'xp_cmdshell', '1'
-- RECONFIGURE WITH OVERRIDE
-- EXEC SP_CONFIGURE 'show advanced option', '1';
-- SHOW THE CONFIGURATION
-- EXEC SP_CONFIGURE;


--turn show advance options off
-- GO
--EXECUTE sp_configure 'show advanced options', 0
-- RECONFIGURE WITH OVERRIDE
-- GO

--HOW-TO ENABLE XP_CMDSHELL END
-------------------------------------------------------------------------

--HOW-TO IMPLEMENT SLEEP
-- sleep for 10 seconds
-- WAITFOR DELAY '00:00:10' SELECT * FROM My_Table

/* LIST ALL PRIMARY KEYS

SELECT
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME AS TABLE_NAME,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME AS COLUMN_NAME,
REPLACE(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE,' ', '_') AS CONSTRAINT_TYPE
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
WHERE
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME <> N'sysdiagrams'
ORDER BY
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME ASC

*/

--HOW-TO COPY TABLE AND THE WHOLE TABLE DATA , COPY TABLE FROM DB TO DB
--==================================================START
/*
use db_Dev
go
drop table tbGui_LinksVisibility

use db_test
go
select *
INTO [db_Dev].[dbo].[tbGui_LinksVisibility]
from [db_TEST].[dbo].[tbGui_LinksVisibility]


*/
--HOW-TO COPY TABLE AND THE WHOLE TABLE DATA , COPY TABLE FROM DB TO DB
--====================================================END
--=================================================== SEE TABLE METADATA START
/*



SELECT c.name AS [COLUMN_NAME], sc.data_type AS [DATA_TYPE], [value] AS
[DESCRIPTION] , c.max_length as [MAX_LENGTH] , c.is_nullable AS [OPTIONAL]
, c.is_identity AS [IS_PRIMARY_KEY] FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id
= c.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and
c.name = sc.column_name
WHERE class = 1 and t.name = 'tbGui_Executedb' ORDER BY SC.DATA_TYPE


*/
--=================================================== SEE TABLE METADATA END
/*
select * from Information_schema.columns
select table_name , column_name from Information_schema.columns where table_name='tbGui_Wizards'
*/


--=================================================== LIST ALL TABLES AND THEIR DESCRIPTOINS START
/*

SELECT T.name AS TableName, CAST(Props.value AS varchar(1000)) AS
TableDescription
FROM sys.tables AS T LEFT OUTER JOIN
(SELECT class, class_desc, major_id, minor_id,
name, value
FROM sys.extended_properties
WHERE (minor_id = 0) AND (class = 1)) AS
Props ON T.object_id = Props.major_id
WHERE (T.type = 'U') AND (T.name <> N'sysdiagrams')
ORDER BY TableName
*/
--=================================================== LIST ALL TABLES AND THEIR DESCRIPTOINS START

--=================================================== LIST ALL OBJECTS FROM DB START
/*


use DB
--HOW-TO LIST ALL PROCEDURE IN A DATABASE
select s.name from sysobjects s where type = 'P'
--HOW-TO LIST ALL TRIGGERS BY NAME IN A DATABASE
select s.name from sysobjects s where type = 'TR'
--HOW-TO LIST TABLES IN A DATABASE
select s.name from sysobjects s where type = 'U'
--how-to list all system tables in a database
select s.name from sysobjects s where type = 's'
--how-to list all the views in a database
select s.name from sysobjects s where type = 'v'


*/

/*
Similarly you can find out other objects created by user, simple change type =

C = CHECK constraint

D = Default or DEFAULT constraint

F = FOREIGN KEY constraint

L = Log

FN = Scalar function

IF = In-lined table-function

P = Stored procedure

PK = PRIMARY KEY constraint (type is K)

RF = Replication filter stored procedure

S = System table

TF = Table function

TR = Trigger

U = User table ( this is the one I discussed above in the example)

UQ = UNIQUE constraint (type is K)

V = View

X = Extended stored procedure
*/



--=================================================== HOW-TO SEE ALL MY PERMISSIONS START


/*

SELECT * FROM fn_my_permissions(NULL, 'SERVER');
USE db_qa;
SELECT * FROM fn_my_permissions (NULL, 'database');
GO

*/
--=================================================== HOW-TO SEE ALL MY PERMISSIONS END

/*
--find table

use db_dev
go
select s.name from sysobjects s where type = 'u' and s.name like '%Visibility%'
select * from tbGui_LinksVisibility

*/

/* find cursor

use db
go
DECLARE @procName varchar(100)
DECLARE @cursorProcNames CURSOR
SET @cursorProcNames = CURSOR FOR
select name from sys.procedures where modify_date > '2009-02-05 13:12:15.273' order by modify_date desc

OPEN @cursorProcNames
FETCH NEXT
FROM @cursorProcNames INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN

set nocount off;
exec sp_HelpText @procName --- or print them
-- print @procName

FETCH NEXT
FROM @cursorProcNames INTO @procName
END
CLOSE @cursorProcNames

select @@error*/


/* -- SEE STORED PROCEDURE EXT PROPS

SELECT ep.name as 'EXT_PROP_NAME' , SP.NAME , [value] as 'DESCRIPTION' FROM sys.extended_properties as ep left join sys.procedures as sp on sp.object_id = ep.major_id where sp.type='P'

-- SEE STORED PROCEDURE EXT PROPS end*/
set nocount off