TODO:
Have you ever wanted to find text in a SQL Agent Job or Job Step?
SOLUTION:
SELECT j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE '%Enter Text Here%'
NOTES:
There are no notes on this topic.
TODO:
Have you ever wanted to create an index using T-SQL. Below is simple syntax to do just that.
SOLUTION:
CREATE INDEX idx_SomeTable_SomeId ON SomeTable(Some_Id)
NOTES:
There are no notes on this topic.
TODO:
Have you ever wanted to update a column to have a default constraint?
SOLUTION:
ALTER TABLE MyTable ADD CONSTRAINT DF_MyTable_IsTest DEFAULT 0 FOR IsTest
NOTES:
The T-SQL above, will add a default constraint, that will default the column 'IsTest' to 0
TODO:
Have you ever wanted to use a CASE statement in a SELECT statement in T-SQL?
SOLUTION:
SELECT Id,
CASE PrimaryColor WHEN NULL THEN
HeaderColor
ELSE
PrimaryColor
END AS HeaderColor
,SectionHeader...
FROM...
WHERE...
NOTES:
This example will evaluate the column 'PrimaryColor'. If it is NULL, then the column 'HeaderColor' will be used. If it does have a value, then 'PrimaryColor' will be used.
TODO:
Have you ever wanted to create a table from a SELECT statement in SQL Server?
SOLUTION:
SELECT * INTO MyTable_TMP
FROM MyTable
NOTES:
There are no notes on this topic.
TODO:
You want to find tables that contain large amounts of data, which can be due to large row counts. The solution will get table stats, and sort by row count descending.
SOLUTION:
SELECT
t.NAME AS 'Table',
i.name as 'Index',
sum(p.rows) as 'Rows',
sum(a.total_pages) as 'Total Pages',
sum(a.used_pages) as 'Used Pages',
sum(a.data_pages) as 'Data Pages',
(sum(a.total_pages) * 8) / 1024 as 'Total Space (MB)',
(sum(a.used_pages) * 8) / 1024 as 'Used Space (MB)',
(sum(a.data_pages) * 8) / 1024 as 'Data Space (MB)'
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name
ORDER BY
Rows DESC
NOTES:
There are no notes on this topic.
TODO:
You have table A, and Table B. Table A has a foreign key to table B, and table B has a foreign key to table A. You need to delete data from table A, but cannot because of the foreign key on Table B.
SOLUTION:
ALTER TABLE employee NOCHECK CONSTRAINT ALL
delete employee where id=100
ALTER TABLE employee WITH CHECK CHECK CONSTRAINT ALL
NOTES:
This will allow you to delete data when there arecircular foreign keys.
TODO:
Have you ever wanted to print output messages in a stored procedure?
SOLUTION:
PRINT 'some string'
PRINT 'some string' + @SomeVariable
NOTES:
Note, if you are concatenating a numeric variable you will need to cast it to Varchar
TODO:
Have you ever wanted to drop a column to an existing SQL Server Table using T-SQL?
SOLUTION:
ALTER TABLE Customer DROP COLUMN FavoriteColor
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to add a column to an existing SQL Server Table using T-SQL?
SOLUTION:
ALTER TABLE Customer ADD FavoriteColor VARCHAR(20)
NOTES:
There are no notes on this topic