exec sp_HelpText procUtils_SqlCheatSheet

  
ALTER PROCEDURE [DBO].[procUtils_SqlCheatSheet]                  
                  
as                   
set nocount on                   
                  
--what was the name of the table with something like role                  
/*                  
SELECT * from sys.tables where [name] like '%POC%'                  
*/                  
-- 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_ExecutePOC'                  
*/                  
                  
-- 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 [POC].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 Poc_Dev                  
go                  
drop table tbGui_LinksVisibility                  
                  
use POc_test                  
go                  
select *                   
INTO [POC_Dev].[ga].[tbGui_LinksVisibility]                  
from [POC_TEST].[ga].[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_ExecutePOC' 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 poc_qa;                  
SELECT * FROM fn_my_permissions (NULL, 'database');                  
GO                  
             
*/                  
--=================================================== HOW-TO SEE ALL MY PERMISSIONS END                  
                
/*                 
--find table                 
                
use poc_dev                 
go                 
select s.name from sysobjects s where type = 'u'  and s.name like '%Visibility%'                
select * from tbGui_LinksVisibility                
                
*/                
                
/* find cursor                
                
use poc        
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*/               



/* UNDERSTANDIGN SET BASED APPROACH 

--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Products'
--Declare the Table variable 
DECLARE @Elements TABLE
(
        Number INT IDENTITY(1,1), --Auto incrementing Identity column
        ProductName VARCHAR(300) --The string value
)

--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT 

--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT

--Populate the TABLE variable using some logic
INSERT INTO @Elements SELECT ProductName FROM dbo.Products

--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1

--Determine the number of rows in the Table
SELECT @Count=max(Number) from @Elements

--A variable to hold the currently selected value from the table
DECLARE @CurrentValue varchar(300);

--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count

BEGIN
    --Load current value from the Table
    SELECT @CurrentValue = ProductName FROM @Elements WHERE Number = @CurrentDelimiterPositionVar
    --Process the current value
    print @CurrentValue
    --Increment loop counter
    SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END

*/
set nocount off