SQL SERVER Metadata
The following posts will include info regarding how to find metadata pertaining to databases and the objects in your databases.
Find the size of your tables
|
|
Need to find info about your tables. Check out sys.allocation_units. See the link below
http://msdn.microsoft.com/en-us/library/ms189792(v=sql.110).aspx
Gives you info regarding total pages used and used pages.
Here is a helpful query to find the size of your tables. Run in the context of the DB in question.
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
http://msdn.microsoft.com/en-us/library/ms189792(v=sql.110).aspx
Gives you info regarding total pages used and used pages.
Here is a helpful query to find the size of your tables. Run in the context of the DB in question.
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
Is Data Compression being used? |
|
If you try to restore from Enterprise to Standard and compression is being used then the restore will fail. Compression info can be found in sys.paritions
Query to find compression
SELECT
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[rows]
,[data_compression_desc]
,[index_id] as [IndexID_on_Table]
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY SchemaName, ObjectName
Query to find compression
SELECT
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[rows]
,[data_compression_desc]
,[index_id] as [IndexID_on_Table]
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY SchemaName, ObjectName
Estimate space savings with data compression |
|
The below text is from technet. A word of CAUTION!!!. I almost brought down production using sp_estimate_data_compression_savings. TempDb blew up!!
The stored procedure sp_estimate_data_compression_savings estimates the amount of space saved by compressing a table and its indexes. It functions by taking a sample of the data and then compressing it in tempdb. Estimate the space savings for the largest tables and indexes in a database, and consider compressing only those tables and indexes that yield significant space savings.
this article explains compression in great detail
http://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
Here is a free tool to help you find data compression savings. Bob Taylor wrote it. Good stuff!!! It's called SQL Server Compression Estimator.
Check out the link below. Really easy to use.
http://ssce.codeplex.com/
The stored procedure sp_estimate_data_compression_savings estimates the amount of space saved by compressing a table and its indexes. It functions by taking a sample of the data and then compressing it in tempdb. Estimate the space savings for the largest tables and indexes in a database, and consider compressing only those tables and indexes that yield significant space savings.
this article explains compression in great detail
http://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
Here is a free tool to help you find data compression savings. Bob Taylor wrote it. Good stuff!!! It's called SQL Server Compression Estimator.
Check out the link below. Really easy to use.
http://ssce.codeplex.com/
SQL Server Build and Version Info |
|
Select @@VERSION will return Version and Build info but what does it all mean. I found a good site to help with this.
http://sqlserverbuilds.blogspot.com/
Here is the link to the info regarding the differences between the different versions of SQL.
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.110).aspx
http://sqlserverbuilds.blogspot.com/
Here is the link to the info regarding the differences between the different versions of SQL.
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.110).aspx
Find the IP of the SQL Server |
|
Maybe you will need to find the IP or other connection properties of your SQL Server. Here you go.
SELECT
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address
The above query will not work on 2005 but this will
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name] ,
SERVERPROPERTY('InstanceName') AS [Instance Name] ,
LOCAL_NET_ADDRESS AS [IP Address Of SQL Server] ,
CLIENT_NET_ADDRESS AS [IP Address Of Client]
FROM SYS.DM_EXEC_CONNECTIONS
WHERE SESSION_ID = @@SPID
SELECT
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address
The above query will not work on 2005 but this will
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name] ,
SERVERPROPERTY('InstanceName') AS [Instance Name] ,
LOCAL_NET_ADDRESS AS [IP Address Of SQL Server] ,
CLIENT_NET_ADDRESS AS [IP Address Of Client]
FROM SYS.DM_EXEC_CONNECTIONS
WHERE SESSION_ID = @@SPID
Memory Used in SQL Server |
|
If you need to know how much memory SQL is consuming at a certain point-in-time use PerfMon or use this query. This will return the Max Server memory setting and memory currently being used.
SELECT
SERVERPROPERTY('SERVERNAME') AS 'Instance',
(SELECT cast(value_in_use as int)/1024.0 FROM sys.configurations WHERE name like '%max server memory%') AS 'Max Server Memory GB',
(SELECT physical_memory_in_use_kb/1024.0/1024 FROM sys.dm_os_process_memory) AS 'SQL Server Memory Usage (GB)',
(SELECT total_physical_memory_kb/1024.0/1024 FROM sys.dm_os_sys_memory) AS 'Physical Memory (GB)',
(SELECT available_physical_memory_kb/1024.0/1024 FROM sys.dm_os_sys_memory) AS 'Available Memory (GB)',
(SELECT system_memory_state_desc FROM sys.dm_os_sys_memory) AS 'System Memory State',
(SELECT ((total_physical_memory_kb - available_physical_memory_kb)*1.0/total_physical_memory_kb*1.00 )*100.00 FROM sys.dm_os_sys_memory) as Mem_Consumed_Percentage,
(SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy') AS 'Page Life Expectancy',
GETDATE() AS 'Data Sample Timestamp'
SELECT
SERVERPROPERTY('SERVERNAME') AS 'Instance',
(SELECT cast(value_in_use as int)/1024.0 FROM sys.configurations WHERE name like '%max server memory%') AS 'Max Server Memory GB',
(SELECT physical_memory_in_use_kb/1024.0/1024 FROM sys.dm_os_process_memory) AS 'SQL Server Memory Usage (GB)',
(SELECT total_physical_memory_kb/1024.0/1024 FROM sys.dm_os_sys_memory) AS 'Physical Memory (GB)',
(SELECT available_physical_memory_kb/1024.0/1024 FROM sys.dm_os_sys_memory) AS 'Available Memory (GB)',
(SELECT system_memory_state_desc FROM sys.dm_os_sys_memory) AS 'System Memory State',
(SELECT ((total_physical_memory_kb - available_physical_memory_kb)*1.0/total_physical_memory_kb*1.00 )*100.00 FROM sys.dm_os_sys_memory) as Mem_Consumed_Percentage,
(SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy') AS 'Page Life Expectancy',
GETDATE() AS 'Data Sample Timestamp'
Number of Processors and RAM |
|
Need to find how many CPUs and Number of Cores and RAM. Here you go.
Below works with 2005
SELECT ( cpu_count / hyperthread_ratio )AS NumberOfPhysicalCPUs
, CASE
WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS NumberOfCoresInEachCPU
, CASE
WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE ( cpu_count / hyperthread_ratio ) * ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS TotalNumberOfCores
, cpu_count AS NumberOfLogicalCPUs
, CONVERT(MONEY, Round(physical_memory_in_bytes / 1073741824.0, 0)) AS TotalRAMInGB
FROM
sys.dm_os_sys_info
Below works with 2012
SELECT ( cpu_count / hyperthread_ratio )AS NumberOfPhysicalCPUs
, CASE
WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS NumberOfCoresInEachCPU
, CASE
WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE ( cpu_count / hyperthread_ratio ) * ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS TotalNumberOfCores
, cpu_count AS NumberOfLogicalCPUs
, ROUND(physical_memory_kb/1024/1024.0,2) AS TotalRAMInGB
FROM
sys.dm_os_sys_info
Below works with 2005
SELECT ( cpu_count / hyperthread_ratio )AS NumberOfPhysicalCPUs
, CASE
WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS NumberOfCoresInEachCPU
, CASE
WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE ( cpu_count / hyperthread_ratio ) * ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS TotalNumberOfCores
, cpu_count AS NumberOfLogicalCPUs
, CONVERT(MONEY, Round(physical_memory_in_bytes / 1073741824.0, 0)) AS TotalRAMInGB
FROM
sys.dm_os_sys_info
Below works with 2012
SELECT ( cpu_count / hyperthread_ratio )AS NumberOfPhysicalCPUs
, CASE
WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS NumberOfCoresInEachCPU
, CASE
WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE ( cpu_count / hyperthread_ratio ) * ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS TotalNumberOfCores
, cpu_count AS NumberOfLogicalCPUs
, ROUND(physical_memory_kb/1024/1024.0,2) AS TotalRAMInGB
FROM
sys.dm_os_sys_info
Find what filegroups your data files belong to! |
|
I have a database with 29 filegroups that is running out of space. Need to add more data files to new storage. So here is the query for current files and there filegroups
SELECT DB_NAME(mf.database_id) AS DBname ,
mf.physical_name ,
fg.name
FROM sys.filegroups fg
JOIN sys.master_files mf ON fg.data_space_id = mf.data_space_id
SELECT DB_NAME(mf.database_id) AS DBname ,
mf.physical_name ,
fg.name
FROM sys.filegroups fg
JOIN sys.master_files mf ON fg.data_space_id = mf.data_space_id
Find the configuration of your server including running services |
|
I had to compare to SQL Instances and see how the differed. I used these sys tables and dmv's
SELECT NAME, value_in_use FROM SYS.configurations
ORDER BY NAME
SELECT servicename, startup_type_desc,service_account
FROM SYS.dm_server_services
SELECT NAME, value_in_use FROM SYS.configurations
ORDER BY NAME
SELECT servicename, startup_type_desc,service_account
FROM SYS.dm_server_services
Check if Schema Exists |
|
I wanted to make my script was robust and check to see if a schema was present in the database prior to executing the rest of the code. So I wrote this..
IF NOT EXISTS( SELECT 1 FROM sys.schemas
WHERE name = '<schema name>')
BEGIN
create schema <schema name>
END
ELSE
PRINT '<schema name> has been created'
The code above will fail being that SQL wants create schema statements in their own batch.
Below is the work around.
IF NOT EXISTS( SELECT 1 FROM sys.schemas
WHERE name = '<schema name>')
BEGIN
EXEC ('create schema <schema name>')
END
ELSE
PRINT '<schema name> has been created'
IF NOT EXISTS( SELECT 1 FROM sys.schemas
WHERE name = '<schema name>')
BEGIN
create schema <schema name>
END
ELSE
PRINT '<schema name> has been created'
The code above will fail being that SQL wants create schema statements in their own batch.
Below is the work around.
IF NOT EXISTS( SELECT 1 FROM sys.schemas
WHERE name = '<schema name>')
BEGIN
EXEC ('create schema <schema name>')
END
ELSE
PRINT '<schema name> has been created'
Find when SQL was started or restarted |
|
Need to find with SQL was started or more importantly restarted...
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
Find Last Execution and Current Status of SQL Server Jobs |
|
This is obsolete if you are using DBATools
Link to the docs on the below cmdlet
Get-DbaAgentJobHistory
I needed to find when a job last ran, when it completed, how many minutes since last completion and if the job was currently running.
Used a function I had never heard of before. dbo.agent_datetime()
It converts datetime info in the msdb job tables. Only good for R2 and up!!
Good stuff.
--delete temp objects if needed
if OBJECT_id('tempdb..#jobresults')is not null
drop table #jobresults
if OBJECT_id('tempdb..#maxtime')is not null
drop table #maxtime
if object_id('tempdb..#xp_results')is not null
drop table #xp_results
if object_id('tempdb..#final')is not null
drop table #final
--temp table for dbo.xp_sqlagent_enum_jobs
CREATE TABLE #xp_results
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
--job execution info using dbo.agent_datetime function
select sj.job_id,sj.name, convert(datetime,dbo.agent_datetime(sjh.run_date,sjh.run_time),120) as UTC_RunTime, run_duration, sjh.run_status
into #jobresults
FROM msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id
WHERE sj.name LIKE '%DB_Backup%' and sj.enabled = 1 AND sjh.step_name = '(Job outcome)' --AND run_status = 1
ORDER BY instance_id desc
--find last job execution info
select *, datediff(mi,UTC_runtime, CURRENT_TIMESTAMP) as Minutes_Since_Last_Execution
into #maxtime
from #jobresults
where UTC_RunTime = (select max(utc_runTime) from #jobresults)
--find current execution status of jobs
insert into #xp_results
EXEC master.dbo.xp_sqlagent_enum_jobs 1,''
--final results set with different measures
select m.name as Job_Name,
m.UTC_RunTime as Job_Start_Time,
m.Minutes_Since_Last_Execution,
dateadd(mi,m.run_duration,m.UTC_Runtime) as Job_Completion_time,
datediff(mi,(dateadd(mi,m.run_duration,m.UTC_Runtime)),current_timestamp) as Minutes_Since_Last_Completion,
xp.running as Current_Execution,
case when xp.running = 1 then 'running'
else 'not running'
end as Current_Execution_Results,
m.run_status as Last_Execution_Status,
case when m.run_status = 0 then 'Failed'
when m.run_status = 1 then 'Succeeded'
when m.run_status = 2 then 'Retry'
when m.run_status = 3 then 'Canceled'
end as Last_Execution_Status_Results
into #final
from #xp_results xp
join #maxtime m on xp.job_id = m.job_id
select * from #final
Link to the docs on the below cmdlet
Get-DbaAgentJobHistory
I needed to find when a job last ran, when it completed, how many minutes since last completion and if the job was currently running.
Used a function I had never heard of before. dbo.agent_datetime()
It converts datetime info in the msdb job tables. Only good for R2 and up!!
Good stuff.
--delete temp objects if needed
if OBJECT_id('tempdb..#jobresults')is not null
drop table #jobresults
if OBJECT_id('tempdb..#maxtime')is not null
drop table #maxtime
if object_id('tempdb..#xp_results')is not null
drop table #xp_results
if object_id('tempdb..#final')is not null
drop table #final
--temp table for dbo.xp_sqlagent_enum_jobs
CREATE TABLE #xp_results
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
--job execution info using dbo.agent_datetime function
select sj.job_id,sj.name, convert(datetime,dbo.agent_datetime(sjh.run_date,sjh.run_time),120) as UTC_RunTime, run_duration, sjh.run_status
into #jobresults
FROM msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id
WHERE sj.name LIKE '%DB_Backup%' and sj.enabled = 1 AND sjh.step_name = '(Job outcome)' --AND run_status = 1
ORDER BY instance_id desc
--find last job execution info
select *, datediff(mi,UTC_runtime, CURRENT_TIMESTAMP) as Minutes_Since_Last_Execution
into #maxtime
from #jobresults
where UTC_RunTime = (select max(utc_runTime) from #jobresults)
--find current execution status of jobs
insert into #xp_results
EXEC master.dbo.xp_sqlagent_enum_jobs 1,''
--final results set with different measures
select m.name as Job_Name,
m.UTC_RunTime as Job_Start_Time,
m.Minutes_Since_Last_Execution,
dateadd(mi,m.run_duration,m.UTC_Runtime) as Job_Completion_time,
datediff(mi,(dateadd(mi,m.run_duration,m.UTC_Runtime)),current_timestamp) as Minutes_Since_Last_Completion,
xp.running as Current_Execution,
case when xp.running = 1 then 'running'
else 'not running'
end as Current_Execution_Results,
m.run_status as Last_Execution_Status,
case when m.run_status = 0 then 'Failed'
when m.run_status = 1 then 'Succeeded'
when m.run_status = 2 then 'Retry'
when m.run_status = 3 then 'Canceled'
end as Last_Execution_Status_Results
into #final
from #xp_results xp
join #maxtime m on xp.job_id = m.job_id
select * from #final
What's up with Physical_Device_Name?? |
|
I am creating a repository of my environments database Servers (SQL and Postgres). This repository will include Sever specs, database config settings and backup locations and history to name a few.
Well for some databases I was getting a GUID type result from msdb.dbo.backupmediafamily.physical_device_name. I thought that was weird. I first thought, that must be a backup device. Only problem is, we don't use backup devices. Did some digging and looked at the device_type and is_snaphost column. The databases in question the device_type was 7 which is a virtual device and is_snaphost = 1. All of these machines are VMs and so I found this blog.
http://www.stuartclowes.com/2011/10/when-is-sql-backup-not-sql-backup.html
Looks like the VMWare snapshot is backing up my DBs. Interesting!!!
SELECT
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupmediafamily.device_type,
msdb.dbo.backupset.is_snapshot,
msdb.dbo.backupset.name AS backupset_name
FROM
msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date
Well for some databases I was getting a GUID type result from msdb.dbo.backupmediafamily.physical_device_name. I thought that was weird. I first thought, that must be a backup device. Only problem is, we don't use backup devices. Did some digging and looked at the device_type and is_snaphost column. The databases in question the device_type was 7 which is a virtual device and is_snaphost = 1. All of these machines are VMs and so I found this blog.
http://www.stuartclowes.com/2011/10/when-is-sql-backup-not-sql-backup.html
Looks like the VMWare snapshot is backing up my DBs. Interesting!!!
SELECT
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupmediafamily.device_type,
msdb.dbo.backupset.is_snapshot,
msdb.dbo.backupset.name AS backupset_name
FROM
msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date
Query to find View Definition |
|
Needed a query to return info about a view, mainly a column list and view definition.
select v.name,
v.type_desc,
cast(v.create_date as varchar(20))as Create_Date,
cast(v.modify_date as varchar(20))as Modified_Date,
SUBSTRING(( SELECT '... ' + b.name
FROM sys.columns b
WHERE b.object_id = c.object_id
FOR
XML PATH('')
), 5, 1000) AS Column_List,
m.definition as View_Definition
from sys.views v
join sys.columns c
on v.object_id = c.object_id
join sys.sql_modules m
on m.object_id = v.object_id
where v.name = '<view name>'
group by v.name, v.type_desc,v.create_date,v.modify_date, m.definition, c.object_id
select v.name,
v.type_desc,
cast(v.create_date as varchar(20))as Create_Date,
cast(v.modify_date as varchar(20))as Modified_Date,
SUBSTRING(( SELECT '... ' + b.name
FROM sys.columns b
WHERE b.object_id = c.object_id
FOR
XML PATH('')
), 5, 1000) AS Column_List,
m.definition as View_Definition
from sys.views v
join sys.columns c
on v.object_id = c.object_id
join sys.sql_modules m
on m.object_id = v.object_id
where v.name = '<view name>'
group by v.name, v.type_desc,v.create_date,v.modify_date, m.definition, c.object_id
Rename Columns with weird characters!! |
|
Was trying to import some data into a database via the import command under tasks. The data was coming from a CSV generated by Python. Was having all kinds of issues including my columns names included double-quotes around each column names. Here was a way to programatically remove the double-quotes from each column name for the various tables. Had the wrap the column name with the quotename function for it to work.
use databasename;
go
declare c cursor local fast_forward
for
select c.name as OldColumnName,replace(c.name,'"','') as NewColumnName, t.name as tablename, s.name as schema_name
from sys.columns c
join sys.tables t on c.object_id = t.object_id
join sys.schemas s on s.schema_id = t.schema_id
where c.name like '"%"'
open c
Declare @oldColumnName varchar(100)
Declare @NewColumnName varchar(100)
Declare @tablename varchar(100)
Declare @schemaname char(3)
Declare @cmd varchar(200)
fetch next from c into @oldColumnName,@NewColumnName,@tablename,@schemaname
while @@FETCH_STATUS = 0
begin
select @cmd = 'exec sp_rename '+'@objname = '+''''+@tablename+'.'+quotename(@oldColumnName)+''''+','+'@newname = '+''''+@NewColumnName+''''+','+'@objtype = '+''''+'COLUMN'+''''+';'
exec (@cmd)
fetch next from c into @oldColumnName,@NewColumnName,@tablename,@schemaname
end
close c
deallocate c
use databasename;
go
declare c cursor local fast_forward
for
select c.name as OldColumnName,replace(c.name,'"','') as NewColumnName, t.name as tablename, s.name as schema_name
from sys.columns c
join sys.tables t on c.object_id = t.object_id
join sys.schemas s on s.schema_id = t.schema_id
where c.name like '"%"'
open c
Declare @oldColumnName varchar(100)
Declare @NewColumnName varchar(100)
Declare @tablename varchar(100)
Declare @schemaname char(3)
Declare @cmd varchar(200)
fetch next from c into @oldColumnName,@NewColumnName,@tablename,@schemaname
while @@FETCH_STATUS = 0
begin
select @cmd = 'exec sp_rename '+'@objname = '+''''+@tablename+'.'+quotename(@oldColumnName)+''''+','+'@newname = '+''''+@NewColumnName+''''+','+'@objtype = '+''''+'COLUMN'+''''+';'
exec (@cmd)
fetch next from c into @oldColumnName,@NewColumnName,@tablename,@schemaname
end
close c
deallocate c
All the info you ever wanted to know about your Agent Jobs |
|
Found this script. Holy smokes it finds everything. Even formats that weird MSDB time formast. Good stuff.
Use MSDB
go SELECT [JobName] = [jobs].[name]
,[Category] = [categories].[name]
,[Owner] = SUSER_SNAME([jobs].[owner_sid])
,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
,[Description] = [jobs].[description]
,[Occurs] =
CASE [schedule].[freq_type]
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQL Server Agent starts'
WHEN 128 THEN 'Start whenever the CPU(s) become idle'
ELSE ''
END
,[Occurs_detail] =
CASE [schedule].[freq_type]
WHEN 1 THEN 'O'
WHEN 4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
WHEN 8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' +
LEFT(
CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END ,
LEN(
CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END
) - 1
)
WHEN 16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
WHEN 32 THEN 'The ' +
CASE [schedule].[freq_relative_interval]
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 4 THEN 'Third'
WHEN 8 THEN 'Fourth'
WHEN 16 THEN 'Last'
END +
CASE [schedule].[freq_interval]
WHEN 1 THEN ' Sunday'
WHEN 2 THEN ' Monday'
WHEN 3 THEN ' Tuesday'
WHEN 4 THEN ' Wednesday'
WHEN 5 THEN ' Thursday'
WHEN 6 THEN ' Friday'
WHEN 7 THEN ' Saturday'
WHEN 8 THEN ' Day'
WHEN 9 THEN ' Weekday'
WHEN 10 THEN ' Weekend Day'
END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
ELSE ''
END
,[Frequency] =
CASE [schedule].[freq_subday_type]
WHEN 1 THEN 'Occurs once at ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 2 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 4 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 8 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
ELSE ''
END
,[AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration])
,[Next_Run_Date] =
CASE [jobschedule].[next_run_date]
WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
END
FROM [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK)
LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK)
ON [jobs].[job_id] = [jobschedule].[job_id]
LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK)
ON [jobschedule].[schedule_id] = [schedule].[schedule_id]
INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK)
ON [jobs].[category_id] = [categories].[category_id]
LEFT OUTER JOIN
( SELECT [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) +
(([run_duration] % 10000) / 100 * 60) +
([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
FROM [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
WHERE [step_id] = 0
GROUP BY [job_id]
) AS [jobhistory]
ON [jobhistory].[job_id] = [jobs].[job_id];
Use MSDB
go SELECT [JobName] = [jobs].[name]
,[Category] = [categories].[name]
,[Owner] = SUSER_SNAME([jobs].[owner_sid])
,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
,[Description] = [jobs].[description]
,[Occurs] =
CASE [schedule].[freq_type]
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQL Server Agent starts'
WHEN 128 THEN 'Start whenever the CPU(s) become idle'
ELSE ''
END
,[Occurs_detail] =
CASE [schedule].[freq_type]
WHEN 1 THEN 'O'
WHEN 4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
WHEN 8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' +
LEFT(
CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END ,
LEN(
CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END +
CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END
) - 1
)
WHEN 16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
WHEN 32 THEN 'The ' +
CASE [schedule].[freq_relative_interval]
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 4 THEN 'Third'
WHEN 8 THEN 'Fourth'
WHEN 16 THEN 'Last'
END +
CASE [schedule].[freq_interval]
WHEN 1 THEN ' Sunday'
WHEN 2 THEN ' Monday'
WHEN 3 THEN ' Tuesday'
WHEN 4 THEN ' Wednesday'
WHEN 5 THEN ' Thursday'
WHEN 6 THEN ' Friday'
WHEN 7 THEN ' Saturday'
WHEN 8 THEN ' Day'
WHEN 9 THEN ' Weekday'
WHEN 10 THEN ' Weekend Day'
END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
ELSE ''
END
,[Frequency] =
CASE [schedule].[freq_subday_type]
WHEN 1 THEN 'Occurs once at ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 2 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 4 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
WHEN 8 THEN 'Occurs every ' +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
ELSE ''
END
,[AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration])
,[Next_Run_Date] =
CASE [jobschedule].[next_run_date]
WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
END
FROM [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK)
LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK)
ON [jobs].[job_id] = [jobschedule].[job_id]
LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK)
ON [jobschedule].[schedule_id] = [schedule].[schedule_id]
INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK)
ON [jobs].[category_id] = [categories].[category_id]
LEFT OUTER JOIN
( SELECT [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) +
(([run_duration] % 10000) / 100 * 60) +
([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
FROM [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
WHERE [step_id] = 0
GROUP BY [job_id]
) AS [jobhistory]
ON [jobhistory].[job_id] = [jobs].[job_id];
Scripted Extended Events Session to find all running queries |
|
I needed to capture the queries on a box due to poor access management. We didn't know who was hitting the box. This will help deconstruct that.
Create PROCEDURE dbo.xe_query_session
as
IF OBJECT_ID('tempdb..#query_data')IS NOT NULL
DROP TABLE #query_data
IF EXISTS(
SELECT *
FROM sys.server_event_sessions
WHERE name = 'Query_Capture'
)
BEGIN
DROP EVENT SESSION [Query_Capture] ON SERVER
End
PRINT 'session starting'
CREATE EVENT SESSION [Query_Capture] ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.sp_statement_starting(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.sql_batch_starting(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.sql_statement_starting(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)
ALTER EVENT SESSION [Query_Capture] ON SERVER STATE=START
WAITFOR DELAY '00:01'
PRINT 'extracting xml'
SELECT CAST(target_data as xml) AS targetdata
INTO #quey_data
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xes
ON xes.address = xet.event_session_address
WHERE xes.name = 'Query_Capture'
AND xet.target_name = 'ring_buffer';
PRINT 'stopping session'
ALTER EVENT SESSION [Query_Capture] ON SERVER STATE=STOP
PRINT 'inserting to xe_query_capture'
INSERT INTO dbo.XE_Query_Capture
( xe_type ,
trns_xact_time ,
username ,
DBName ,
ObjectID ,
ObjectName ,
SQL ,
Batch_text ,
Client_App_Name ,
Client_Hostname,
Physical_reads ,
logical_reads ,
duration
)SELECT
xed.event_data.value('(@name)[1]', 'varchar(100)') as xe_type,
xed.event_data.value('(@timestamp)[1]', 'datetime2') as trns_xact_time,
xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(100)') as username,
xed.event_data.value('(action[@name="database_name"]/value)[1]', 'varchar(100)') as DBName,
xed.event_data.value('(data[@name="object_id"]/value)[1]', 'varchar(max)') as objectID,
xed.event_data.value('(data[@name="object_name"]/value)[1]', 'varchar(max)') as objectName,
xed.event_data.value('(data[@name="statement"]/value)[1]', 'varchar(max)') as sql,
xed.event_data.value('(data[@name="batch_text"]/value)[1]', 'varchar(max)') as batch_text,
xed.event_data.value('(action[@name="client_app_name"]/value)[1]', 'varchar(100)') as client_app_name,
xed.event_data.value('(action[@name="client_hostname"]/value)[1]', 'varchar(100)') as client_hostname,
xed.event_data.value('(data[@name="physical_reads"]/value)[1]', 'varchar(30)') as physical_reads,
xed.event_data.value('(data[@name="logical_reads"]/value)[1]', 'varchar(30)') as logical_reads,
xed.event_data.value('(data[@name="duration"]/value)[1]', 'varchar(30)') as duration
FROM #query_data
cross apply targetdata.nodes('//RingBufferTarget/event') as xed(event_data)
PRINT 'cleanup'
DROP TABLE #capture_waits_data
DROP EVENT SESSION [Query_Capture] ON SERVER
Capping the Size of Your Data Files |
|
Had to cap the size of 30 database files. Didn't want to type all that. Here is the code to generate the Alter Database statement. It also adds a GB to the file when setting the MAXSIZE parameter.
SELECT DB_NAME(database_id) ,
name ,
physical_name ,
size * 8 / 1024 / 1024.0 AS DB_File_Size_GB ,
CASE max_size
WHEN -1 THEN 'UNLIMITED'
ELSE STR(max_size * 8.0 / 1024 / 1024, 10, 2)
END AS MAX_SIZE_GB ,
CASE WHEN is_percent_growth = 0 THEN STR(growth * 8.0 / 1024, 10, 1)
WHEN is_percent_growth = 1 THEN STR(growth, 10, 0) + '%'
END AS Growth ,
is_percent_growth ,
'alter database ' + CAST(DB_NAME(database_id) AS VARCHAR(20))
+ CHAR(13) + 'modify file ' + CHAR(13) + '(name = ' + name + ','
+ CHAR(13) + 'maxsize = ' + LTRIM(STR(( size * 8 / 1024 / 1024.0 + 1),10, 0)) + ' GB)' + CHAR(13)
+ '------' + CHAR(10)
FROM sys.master_files
SELECT DB_NAME(database_id) ,
name ,
physical_name ,
size * 8 / 1024 / 1024.0 AS DB_File_Size_GB ,
CASE max_size
WHEN -1 THEN 'UNLIMITED'
ELSE STR(max_size * 8.0 / 1024 / 1024, 10, 2)
END AS MAX_SIZE_GB ,
CASE WHEN is_percent_growth = 0 THEN STR(growth * 8.0 / 1024, 10, 1)
WHEN is_percent_growth = 1 THEN STR(growth, 10, 0) + '%'
END AS Growth ,
is_percent_growth ,
'alter database ' + CAST(DB_NAME(database_id) AS VARCHAR(20))
+ CHAR(13) + 'modify file ' + CHAR(13) + '(name = ' + name + ','
+ CHAR(13) + 'maxsize = ' + LTRIM(STR(( size * 8 / 1024 / 1024.0 + 1),10, 0)) + ' GB)' + CHAR(13)
+ '------' + CHAR(10)
FROM sys.master_files
Extended Properties |
|
I have seen in the table definition when scripting out a table from SSMS....
For more info see this link..
https://www.mssqltips.com/sqlservertip/5384/working-with-sql-server-extended-properties/
SELECT SCHEMA_NAME(tbl.schema_id) AS SchemaName ,
tbl.name AS TableName ,
clmns.name AS ColumnName ,
p.name AS ExtendedPropertyName ,
CAST(p.value AS SQL_VARIANT) AS ExtendedPropertyValue
FROM sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id
INNER JOIN sys.extended_properties AS p ON p.major_id = tbl.object_id
AND p.minor_id = clmns.column_id
AND p.class = 1
- sys.sp_addextendedproperty
For more info see this link..
https://www.mssqltips.com/sqlservertip/5384/working-with-sql-server-extended-properties/
SELECT SCHEMA_NAME(tbl.schema_id) AS SchemaName ,
tbl.name AS TableName ,
clmns.name AS ColumnName ,
p.name AS ExtendedPropertyName ,
CAST(p.value AS SQL_VARIANT) AS ExtendedPropertyValue
FROM sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id
INNER JOIN sys.extended_properties AS p ON p.major_id = tbl.object_id
AND p.minor_id = clmns.column_id
AND p.class = 1
Find what Store Procedures are dependent on User-Defined Data Types |
|
DB I am currently working has a bunch of user-defined data types. I need to drop them for an upgrade process we are automating.
This query will give you that info.
SELECT
OBJECT_NAME(sed.referencing_id) AS Dependent_SP_Name,
sed.referenced_entity_name AS user_defined_type
FROM sys.sql_expression_dependencies sed
JOIN sys.procedures p ON p.object_id = sed.referencing_id
WHERE sed.referenced_class_desc = 'type';
This query will give you that info.
SELECT
OBJECT_NAME(sed.referencing_id) AS Dependent_SP_Name,
sed.referenced_entity_name AS user_defined_type
FROM sys.sql_expression_dependencies sed
JOIN sys.procedures p ON p.object_id = sed.referencing_id
WHERE sed.referenced_class_desc = 'type';
Script out Triggers |
|
SELECT name, 'drop trigger '+obj.name+';', [definition] FROM sys.sql_modules m
inner join sys.objects obj on obj.object_id=m.object_id
where obj.type ='TR' AND name = '<trigger name>'
inner join sys.objects obj on obj.object_id=m.object_id
where obj.type ='TR' AND name = '<trigger name>'
Find out how often a trigger is fired |
|
If you need info on triggers in your DB this will help you find that info. There is a bug in the execution count, so don't trust that value. Also, this applies to cached plans. If that plan is not found in cache then no bueno, but once it's found in cache again, all the counters are reset.
SELECT d.object_id, d.database_id, DB_NAME(database_id) AS 'database_name',
OBJECT_NAME(object_id, database_id) AS 'trigger_name', d.cached_time,
d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_trigger_stats AS d
ORDER BY [total_worker_time] DESC;
Also here is a great article on the Trigger debate.
www.red-gate.com/simple-talk/sql/t-sql-programming/triggers-threat-menace/
SELECT d.object_id, d.database_id, DB_NAME(database_id) AS 'database_name',
OBJECT_NAME(object_id, database_id) AS 'trigger_name', d.cached_time,
d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_trigger_stats AS d
ORDER BY [total_worker_time] DESC;
Also here is a great article on the Trigger debate.
www.red-gate.com/simple-talk/sql/t-sql-programming/triggers-threat-menace/
SQL Server Perfmon Counters not found |
|
SQL Server perfmon counters we not found on several of my boxes.
Here is a quick article on the fix
https://www.itprotoday.com/sql-server/q-restore-missing-sql-server-performance-counters
Here is a quick article on the fix
https://www.itprotoday.com/sql-server/q-restore-missing-sql-server-performance-counters