SqlServerSystemMisc.sql

-- find tables by name
SELECT  *
FROM    sys.objects
WHERE   type = 'U'
        AND [name] LIKE '%xxx%'
ORDER BY 1
 
-- find tables with specified column
SELECT DISTINCT o.name
FROM    sys.columns c
JOIN    sys.objects o
    ON o.object_id = c.object_id
WHERE   c.NAME LIKE 'xxx'
 
 
-- get columns in table
SELECT o.name, c.name FROM sys.columns c
JOIN sys.objects o ON o.object_id = c.object_id
WHERE o.NAME = 'ws_load_col_v'  
 
 
-- get columns in table, including type, size, is_nullable
SELECT  o.name TableName,
        c.name ColumnName,
        t.name DataType,
        c.max_length, c.is_nullable, c.* FROM sys.columns c
JOIN sys.objects o ON o.object_id = c.object_id
JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.NAME = 'ws_load_col_v' ORDER BY column_id
 
 
-- get indexes on table
SELECT o.name, i.* FROM sys.indexes i
JOIN  sys.objects o ON o.object_id = i.object_id
WHERE o.NAME = 'xxx'
 
 
-- system processes (sp_who2)
SELECT * FROM sys.sysprocesses
WHERE 
	loginame like 'domain\user%'
	AND program_name NOT LIKE 'Microsoft SQL Server Management Studio%'  -- exclude ssms

 
-- find stored procs that contains specified text
SELECT DISTINCT OBJECT_SCHEMA_NAME(id), OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%xxx%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1
--    AND OBJECT_NAME(id)  LIKE 'update%'
ORDER BY 1
 
 
-- get stored proc text (code)
EXEC sp_helptext 'xxx.yyy'
 
 
-- insert stored proc text into table variable (also works for temp table and real table)
DECLARE @procText TABLE (Text VARCHAR(MAX))
INSERT INTO @procText(Text) EXEC sp_helpText 'xxx.yyy'
SELECT * FROM @procText
 
-- get SQL Server Version
SELECT @@version