Add on Find Your Tab
Rédigé par Sozezzo - - Aucun commentaireA browser add-on to help you find a tab easier
https://addons.mozilla.org/en-CA/firefox/addon/find-your-tab/
A browser add-on to help you find a tab easier
https://addons.mozilla.org/en-CA/firefox/addon/find-your-tab/
SQL Script to export table data in xml files, or export schema data to xsd files
This use OLE Automation Stored Procedures (Transact-SQL), and maybe, you need to change the global configuration settings of SQL Server. It can be an issue in production environment, and this case.
The SQL Script have three stored procedures :
#spWriteStringToFile
#spExportTableToXmlFile
#spExportDatabaseToXmlFile
Check GitHub for last version : https://github.com/sozezzo/SqlServer/
You run #spExportDatabaseToXmlFile, and you can filter by the table name or the scheme name.
The filter can use wildcard character : %
Limitation :
Some column type cannot be exported. ( Ex. Type := Geography )
You can change the column type, or copy the data to a new table with transformed columns.
Suggestions
Backup and restore the database over another SQL Server, it is preferable to use develop SQL Server.
Use shared folder with low security to storage xml files.
Fix Error : SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures'
GO exec sp_configure 'show advanced options', 1 GO RECONFIGURE; GO exec sp_configure 'Ole Automation Procedures', 1 GO RECONFIGURE; GO exec sp_configure 'show advanced options', 1 GO RECONFIGURE; GO
Sql Script - Check last line to change the configuration
GO /* * Name : #spFileDetails * Description : Get info file * * Syntax : * EXEC #spFileDetails 'C:MyFile.txt'; * */ CREATE OR ALTER PROCEDURE #spFileDetails ( @Filename VARCHAR(100) /* spFileDetails 'c:autoexec.bat' */ ) AS BEGIN -- https://www.red-gate.com/simple-talk/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/ SET NOCOUNT ON; DECLARE @hr INT , --the HRESULT returned from @objFileSystem INT , --the FileSystem object @objFile INT , --the File object @ErrorObject INT , --the error object @ErrorMessage VARCHAR(255) , --the potential error message @Path VARCHAR(100) , -- @ShortPath VARCHAR(100) , @Type VARCHAR(100) , @DateCreated datetime , @DateLastAccessed datetime , @DateLastModified datetime , @Attributes INT , @size INT SET nocount ON SELECT @hr=0 , @errorMessage='opening the file system object ' EXEC @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT IF @hr=0 SELECT @errorMessage='accessing the file ''' COLLATE DATABASE_DEFAULT +@Filename+'''' , @ErrorObject=@objFileSystem IF @hr=0 EXEC @hr = sp_OAMethod @objFileSystem , 'GetFile' , @objFile out , @Filename IF @hr=0 SELECT @errorMessage='getting the attributes of ''' COLLATE DATABASE_DEFAULT +@Filename+'''' , @ErrorObject=@objFile IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'Path' , @path OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'ShortPath' , @ShortPath OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'Type' , @Type OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'DateCreated' , @DateCreated OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'DateLastAccessed' , @DateLastAccessed OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'DateLastModified' , @DateLastModified OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'Attributes' , @Attributes OUT IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile , 'size' , @size OUT IF @hr<>0 BEGIN DECLARE @Source VARCHAR(255) , @Description VARCHAR(255) , @Helpfile VARCHAR(255) , @HelpID INT EXECUTE sp_OAGetErrorInfo @errorObject , @source OUTPUT , @Description OUTPUT , @Helpfile OUTPUT , @HelpID OUTPUT SELECT @ErrorMessage='Error whilst ' COLLATE DATABASE_DEFAULT +@Errormessage+', ' +@Description RAISERROR (@ErrorMessage,16,1) END EXEC sp_OADestroy @objFileSystem EXEC sp_OADestroy @objFile SELECT [Path]= @Path , [ShortPath]= @ShortPath , [Type]= @Type , [DateCreated]= @DateCreated , [DateLastAccessed]= @DateLastAccessed , [DateLastModified]= @DateLastModified , [Attributes]= @Attributes , [size]= @size RETURN @hr END GO GO /* * Name : #spWriteStringToFile * Description : Write in text file * * Syntax : * EXEC #spWriteStringToFile @String = 'Hello Word!', @Path = 'C:Temp', @Filename = 'myfile.txt'; * */ CREATE OR ALTER PROCEDURE #spWriteStringToFile ( @String VARCHAR(MAX) , @Path VARCHAR(512) , @Filename VARCHAR(512) ) AS BEGIN SET NOCOUNT ON; DECLARE @objFileSystem INT , @objTextStream INT , @objErrorObject INT , @strErrorMessage VARCHAR(1000) , @Command VARCHAR(1000) , @hr INT , @fileAndPath VARCHAR(512); SET NOCOUNT ON; SELECT @strErrorMessage = 'opening the File System Object'; EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT; SELECT @FileAndPath = @path + '' COLLATE DATABASE_DEFAULT + @filename; IF @HR = 0 SELECT @objErrorObject = @objFileSystem , @strErrorMessage = 'Creating file "' COLLATE DATABASE_DEFAULT + @FileAndPath + '"' COLLATE DATABASE_DEFAULT ; IF @HR = 0 EXECUTE @hr = sp_OAMethod @objFileSystem , 'CreateTextFile' , @objTextStream OUT , @FileAndPath , 2 , True; IF @HR = 0 SELECT @objErrorObject = @objTextStream , @strErrorMessage = 'writing to the file "' COLLATE DATABASE_DEFAULT + @FileAndPath + '"' COLLATE DATABASE_DEFAULT ; IF @HR = 0 EXECUTE @hr = sp_OAMethod @objTextStream , 'Write' , NULL , @String; IF @HR = 0 SELECT @objErrorObject = @objTextStream , @strErrorMessage = 'closing the file "' COLLATE DATABASE_DEFAULT + @FileAndPath + '"' COLLATE DATABASE_DEFAULT ; IF @HR = 0 EXECUTE @hr = sp_OAMethod @objTextStream , 'Close'; IF @hr <> 0 BEGIN DECLARE @Source VARCHAR(255) , @Description VARCHAR(255) , @Helpfile VARCHAR(255) , @HelpID INT; EXECUTE sp_OAGetErrorInfo @objErrorObject , @source OUTPUT , @Description OUTPUT , @Helpfile OUTPUT , @HelpID OUTPUT; SELECT @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something' COLLATE DATABASE_DEFAULT ) + ', ' COLLATE DATABASE_DEFAULT + COALESCE(@Description, '' COLLATE DATABASE_DEFAULT ); RAISERROR(@strErrorMessage, 16, 1); END; EXECUTE sp_OADestroy @objTextStream; EXECUTE sp_OADestroy @objFileSystem; END; GO GO /* * Name : #spExportTableToXmlFile * Description : Export table in xml file * * Use stored procedure : #spWriteStringToFile * * Syntax : * EXEC #spExportTableToXmlFile @SchemaName = 'dbo', @TableName = 'User', @Path = 'C:Temp', @Filename = 'user.xml'; * */ CREATE OR ALTER PROCEDURE #spExportTableToXmlFile ( @SchemaName VARCHAR(255) , @TableName VARCHAR(255) , @Path VARCHAR(255) , @FileName VARCHAR(512) = NULL , @SafeMode INT = 0 , @CreateXSDFile INT = 0 , @Debug INT = 0 ) AS BEGIN SET NOCOUNT ON; IF (@Filename IS NULL) BEGIN SET @Filename = @SchemaName+'.' COLLATE DATABASE_DEFAULT +@TableName+'.xml' COLLATE DATABASE_DEFAULT; IF (@CreateXSDFile = 1) SET @Filename = @SchemaName+'.' COLLATE DATABASE_DEFAULT +@TableName+'.xsd' COLLATE DATABASE_DEFAULT; END -- TODO : Clean up invalided chars DECLARE @Template NVARCHAR(MAX) = ' PRINT ''-- Export Table to Xml File : [@(SchemaName)].[@(TableName)]'' PRINT ''-- '' + CONVERT(VARCHAR(50), GETDATE(), 121); DECLARE @xml varchar(max); SET @xml = (select * from [@(SchemaName)].[@(TableName)] FOR XML AUTO, ROOT (''@(SchemaName).@(TableName)'')) EXEC #spWriteStringToFile @String = @xml, @Path = ''@(Path)'', @Filename = ''@(Filename)''; ' COLLATE DATABASE_DEFAULT IF (@CreateXSDFile = 1) BEGIN SET @Template = ' PRINT ''-- Export Table to Xml File : [@(SchemaName)].[@(TableName)]'' PRINT ''-- '' + CONVERT(VARCHAR(50), GETDATE(), 121); DECLARE @xml varchar(max); SET @xml = (select TOP 0 * from [@(SchemaName)].[@(TableName)] FOR XML AUTO, ELEMENTS, XMLSCHEMA ) EXEC #spWriteStringToFile @String = @xml, @Path = ''@(Path)'', @Filename = ''@(Filename)''; ' COLLATE DATABASE_DEFAULT END SET @Template = REPLACE(@Template,'@(SchemaName)' COLLATE DATABASE_DEFAULT , @SchemaName); SET @Template = REPLACE(@Template,'@(TableName)' COLLATE DATABASE_DEFAULT , @TableName); SET @Template = REPLACE(@Template,'@(Path)' COLLATE DATABASE_DEFAULT , @Path); SET @Template = REPLACE(@Template,'@(Filename)' COLLATE DATABASE_DEFAULT , @Filename); IF (@Debug = 1) PRINT @Template IF (@SafeMode = 1) BEGIN BEGIN TRY EXEC (@Template) END TRY BEGIN CATCH PRINT '-----------------------------------' COLLATE DATABASE_DEFAULT PRINT '-- WARNING - Safe Mode' COLLATE DATABASE_DEFAULT PRINT '-- Table cannot be exported : ' COLLATE DATABASE_DEFAULT + @SchemaName +'.'+ @TableName PRINT '-----------------------------------' COLLATE DATABASE_DEFAULT END CATCH END ELSE BEGIN EXEC (@Template); END END GO GO /* * Name : #spExportDatabaseToXmlFile * Description : Export database tables in xml files * * Use stored procedure : #spExportTableToXmlFile, #spWriteStringToFile * * Syntax : * EXEC #spExportTableToXmlFile @Path = 'C:Temp'; * EXEC #spExportTableToXmlFile @Path = 'C:Temp', @TableFilter = 'Test%'; * EXEC #spExportTableToXmlFile @Path = 'C:Temp', @SchemaFilter = '%dbo%', @TableFilter = 'Test%'; * */ CREATE OR ALTER PROCEDURE #spExportDatabaseToXmlFile ( @Path VARCHAR(1024) , @SchemaFilter VARCHAR(512) = NULL , @TableFilter VARCHAR(512) = NULL , @CreateXSDFile INT = 0 , @SafeMode INT = 0 , @Debug INT = 0 ) AS BEGIN SET NOCOUNT ON; DECLARE @SchemaName VARCHAR(512); DECLARE @TableName VARCHAR(512); SELECT s.name AS SchemaName , t.name AS TableName INTO #TableToExport FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE ( @SchemaFilter IS NULL OR s.name LIKE @SchemaFilter) AND ( @TableFilter IS NULL OR t.name LIKE @TableFilter) WHILE (EXISTS (SELECT * FROM #TableToExport)) BEGIN SELECT TOP 1 @SchemaName = SchemaName, @TableName = TableName FROM #TableToExport; DELETE FROM #TableToExport WHERE @SchemaName = SchemaName AND @TableName = TableName; EXEC #spExportTableToXmlFile @SchemaName = @SchemaName , @TableName = @TableName , @Path = @Path , @CreateXSDFile = @CreateXSDFile , @SafeMode = @SafeMode , @Debug = @Debug; END END GO -- exec #spExportDatabaseToXmlFile @Path = 'C:TEMP'; -- Export data -- exec #spExportDatabaseToXmlFile @Path = 'C:TEMP', @SafeMode = 1; -- Export data & Safe mode -- exec #spExportDatabaseToXmlFile @Path = 'C:TEMP', @CreateXSDFile = 1, @Debug = 1; -- Export schema
Source :
(alternative solution with a lot steps and things to do)
https://www.easeus.com/sql-database-recovery/sql-to-xml.html
This PowerShell script generates an html report listing all accounts used as logon account by services on servers in an Active Directory domain.
The script has filters to ignore accounts : NT Service, NT AUTHORITY and LocalSystem
Apply filter by servers and services
$FilterServerLike = "*" ## Select all servers $FilterServiceLike = "*" ## Select all services
Ignore accounts
$IgnoreAccount_NT_Service = 1 ## NT Service $IgnoreAccount_NT_AUTHORITY = 1 ## NT AUTHORITY $IgnoreAccount_LocalSystem = 1 ## LocalSystem
Powershell script to find servers and services
<# report-service-server.ps1 Reads service configuration from all Windows servers in the current domain and generates report listing all service logon account. Version history: 08.08.2020 First release #Reference : https://gallery.technet.microsoft.com/scriptcenter/PowerShell-script-to-find-6fc15ecb * Use Job and missing nice features #> Clear ## Begin Configuration ## $FilterServerLike = "*" ## Filter server $FilterServiceLike = "*" ## Filter service ## Search for all server and all services ## #$FilterServerLike = "**" ## Search all server : "*" #$FilterServiceLike = "*" ## Search all service ## Search for all server *sql* and all services mssql* ## #$FilterServerLike = "*sql*" ## Search all server with name *SQL* #$FilterServiceLike = "MSSQL*" ## Search service MSSQL* $IgnoreAccount_NT_Service = 1 ## NT Service $IgnoreAccount_NT_AUTHORITY = 1 ## NT AUTHORITY $IgnoreAccount_LocalSystem = 1 ## LocalSystem $ListServer = 1 ## Create table -- Server | #Services | Access status $reportFile = "$env:TEMP\report_service_server.html" ## End Configuration ## ## Global variables ## $ErrorActionPreference = "Stop" $currentDomain = $env:USERDOMAIN.ToUpper() $ServiceList = @{} $ServerList = @{} [string[]]$warnings = @() function get-server-info() { param( $hostname ) if ( Test-Connection -ComputerName $hostname -Count 3 -Quiet ){ try { # retrieve service list form a remove machine $serviceList = @( gwmi -Class Win32_Service -ComputerName $hostname -Property Name,StartName,SystemName -ErrorAction Stop ) ##$serviceList ############################## # reads service list if ( $serviceList.GetType() -eq [Object[]] ){ try { $serviceList = $serviceList | ? { $_.StartName.toUpper() } if ($serviceList.Count -ge 1) { $arrID = $script:ServerList.Count+1 $ItemInfo = $hostname, $($serviceList.Count), "" $script:ServerList.Add( $arrID, @( $ItemInfo ) ) } else { $arrID = $script:ServerList.Count+1 $ItemInfo = $hostname, "", "no services" $script:ServerList.Add( $arrID, @( $ItemInfo ) ) } #Apply Filter if ($IgnoreAccount_NT_Service -eq 1) { $serviceList = $serviceList | Where-Object {$_.StartName -notlike "NT Service*" } | ? { $_.StartName } } if ($IgnoreAccount_NT_AUTHORITY -eq 1) { $serviceList = $serviceList | Where-Object {$_.StartName -notlike "NT AUTHORITY*" } | ? { $_.StartName } } if ($IgnoreAccount_LocalSystem -eq 1) { $serviceList = $serviceList | Where-Object {$_.StartName -notlike "LocalSystem" } | ? { $_.StartName } } if ($FilterServiceLike -ne "*") { $serviceList = $serviceList | Where-Object {$_.Name -like $FilterServiceLike } } foreach( $service in $serviceList ){ $arrID = $script:ServiceList.Count+1 $ItemInfo = $service.StartName, $($service.Name), $($service.SystemName) $script:ServiceList.Add( $arrID, @( $ItemInfo ) ) } } catch {} } elseif ( $data.GetType() -eq [String] ) { $script:warnings += "Fail to read service info" } } catch { $global:warnings += @("$hostname | Failed to retrieve data $($_.toString())") $arrID = $script:ServerList.Count+1 $ItemInfo = $hostname, "", "Failed" $script:ServerList.Add( $arrID, @( $ItemInfo ) ) } } else { $global:warnings += @("$hostname | unreachable") $arrID = $script:ServerList.Count+1 $ItemInfo = $hostname, "", "Unreachable" $script:ServerList.Add( $arrID, @( $ItemInfo ) ) } } ################# MAIN ################# ## Add-WindowsFeature RSAT-AD-PowerShell Import-Module ActiveDirectory # read computer accounts from current domain Write-Progress -Activity "Retrieving server list from ActiveDirectory" -Status "Processing..." -PercentComplete 0 $serverServiceList = Get-ADComputer -Filter {OperatingSystem -like "Windows Server*"} -Properties DNSHostName, cn | Where-Object {$_.Name -like $FilterServerLike } | ? { $_.enabled } $count_servers = 0 foreach( $server in $serverServiceList ){ $dnshostname = $server.dnshostname $dnshostname ++$count_servers Write-Progress -Activity "Retrieving data from server $dnshostname ( $count_servers / $($serverServiceList.Count) ) " -Status "Processing..." -PercentComplete ( $count_servers * 100 / $serverServiceList.Count ) get-server-info $server.dnshostname } # prepare data table for report Write-Progress -Activity "Generating report" -Status "Please wait..." -PercentComplete 0 $ServiceTable = @() foreach( $value in $serviceList.Values ) { $row = new-object psobject Add-Member -InputObject $row -MemberType NoteProperty -Name "Account" -Value $(($value)[0]) Add-Member -InputObject $row -MemberType NoteProperty -Name "Service" -Value $(($value)[1]) Add-Member -InputObject $row -MemberType NoteProperty -Name "Server" -Value $(($value)[2]) $ServiceTable += $row } $ServerTable = @() foreach( $value in $ServerList.Values ) { $row = new-object psobject Add-Member -InputObject $row -MemberType NoteProperty -Name "Server" -Value $(($value)[0]) Add-Member -InputObject $row -MemberType NoteProperty -Name "Services" -Value $(($value)[1]) Add-Member -InputObject $row -MemberType NoteProperty -Name "Status" -Value $(($value)[2]) $ServerTable += $row } ################# # create report $datenow = Get-Date -format "yyyy-MMM-dd HH:mm" $report = " <!DOCTYPE html> <html> <head> <style> TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;white-space:nowrap;} TH{border-width: 1px;padding: 4px;border-style: solid;border-color: black} TD{border-width: 1px;padding: 2px 10px;border-style: solid;border-color: black} </style> </head> <body> <H1>Service & Server report for $currentDomain domain</H1> <H3>Server Filter : $FilterServerLike</br> Service Filter : $FilterServiceLike</br> Login : $env:UserDomain$env:UserName</br> Date : $datenow </H3> <H2>Discovered service accounts</H2> $( $ServiceTable | Sort Account | ConvertTo-Html Account, Service, Server -Fragment ) Discovered $($ServiceTable.count) services. </br> <H2>Discovered servers</H2> $( $ServerTable | Sort Status, Server | ConvertTo-Html Server, Services, Status -Fragment ) $($serverList.count) servers processed. </br> <H2>Warning messages</H2> $( $warnings | % { "<p>$_</p>" } ) </body> </html>" Write-Progress -Activity "Generating report" -Status "Please wait..." -Completed $report | Set-Content $reportFile -Force Invoke-Expression $reportFile
https://gallery.technet.microsoft.com/scriptcenter/PowerShell-script-to-find-6fc15ecb
This query returns list of tables and their columns with details.
SELECT @@Servername AS ServerName , DB_NAME() AS DatabaseName , DB_ID() AS DatabaseId , sc.name AS SchemaName , tab.schema_id AS SchemaId , tab.NAME AS TableName , tab.object_id AS TableId , col.NAME AS ColumnName , col.column_id , t.NAME AS DataTypeName , CASE WHEN t.NAME IN('nchar', 'nvarchar') THEN col.max_length/2 ELSE col.max_length END AS Length_Size , t.max_length , CAST(col.is_identity AS INT) AS is_identity , col.scale , t.precision AS Precision , t.NAME + CASE WHEN t.is_user_defined = 0 THEN ISNULL('(' + CASE WHEN t.NAME IN('binary', 'char', 'nchar', 'varchar', 'nvarchar', 'varbinary') THEN CASE col.max_length WHEN -1 THEN 'MAX' ELSE CASE WHEN t.NAME IN('nchar', 'nvarchar') THEN CAST(col.max_length / 2 AS VARCHAR(4)) ELSE CAST(col.max_length AS VARCHAR(4)) END END WHEN t.NAME IN('datetime2', 'datetimeoffset', 'time') THEN CAST(col.scale AS VARCHAR(4)) WHEN t.NAME IN('decimal', 'numeric') THEN CAST(col.precision AS VARCHAR(4)) + ', ' + CAST(col.scale AS VARCHAR(4)) END + ')', '') ELSE ':' + ( SELECT c_t.NAME + ISNULL('(' + CASE WHEN c_t.NAME IN('binary', 'char', 'nchar', 'varchar', 'nvarchar', 'varbinary') THEN CASE c.max_length WHEN -1 THEN 'MAX' ELSE CASE WHEN t.NAME IN('nchar', 'nvarchar') THEN CAST(c.max_length / 2 AS VARCHAR(4)) ELSE CAST(c.max_length AS VARCHAR(4)) END END WHEN c_t.NAME IN('datetime2', 'datetimeoffset', 'time') THEN CAST(c.scale AS VARCHAR(4)) WHEN c_t.NAME IN('decimal', 'numeric') THEN CAST(c.precision AS VARCHAR(4)) + ', ' + CAST(c.scale AS VARCHAR(4)) END + ')', '') FROM sys.columns AS c INNER JOIN sys.types AS c_t ON c.system_type_id = c_t.user_type_id WHERE c.object_id = col.object_id AND c.column_id = col.column_id AND c.user_type_id = col.user_type_id ) END AS [DataType] , CASE WHEN col.collation_name IS NOT NULL THEN 1 ELSE 0 END has_collation_name , col.collation_name , col.is_nullable , CASE WHEN col.is_nullable = 0 THEN 'N' ELSE 'Y' END AS nullable , CASE WHEN def.DEFINITION IS NOT NULL THEN 1 ELSE 0 END AS has_default_value , CASE WHEN def.DEFINITION IS NOT NULL THEN def.DEFINITION ELSE '' END AS default_value , CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_primary_key , CASE WHEN pk.column_id IS NOT NULL THEN 'PK' ELSE '' END AS primary_key , CASE WHEN fk.parent_column_id IS NOT NULL THEN 1 ELSE 0 END AS is_foreign_key , CASE WHEN fk.parent_column_id IS NOT NULL THEN 'FK' ELSE '' END AS foreign_key , CASE WHEN uk.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_unique_key , CASE WHEN uk.column_id IS NOT NULL THEN 'UK' ELSE '' END AS unique_key , CASE WHEN ch.check_const IS NOT NULL THEN 1 ELSE 0 END AS is_check_contraint , CASE WHEN ch.check_const IS NOT NULL THEN ch.check_const ELSE '' END AS check_contraint , col.is_computed , cc.DEFINITION AS computed_column_definition , ep.value AS comments FROM sys.computed_columns AS cc RIGHT OUTER JOIN sys.tables AS tab INNER JOIN sys.schemas AS sc ON tab.schema_id = sc.schema_id LEFT OUTER JOIN sys.columns AS col ON tab.object_id = col.object_id LEFT OUTER JOIN sys.types AS t ON col.user_type_id = t.user_type_id LEFT OUTER JOIN sys.default_constraints AS def ON def.object_id = col.default_object_id LEFT OUTER JOIN (SELECT sys.index_columns.object_id , sys.index_columns.column_id FROM sys.index_columns INNER JOIN sys.indexes ON sys.index_columns.object_id = sys.indexes.object_id AND sys.index_columns.index_id = sys.indexes.index_id WHERE (sys.indexes.is_primary_key = 1)) AS pk ON col.object_id = pk.object_id AND col.column_id = pk.column_id LEFT OUTER JOIN (SELECT fc.parent_column_id , fc.parent_object_id FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id GROUP BY fc.parent_column_id , fc.parent_object_id) AS fk ON fk.parent_object_id = col.object_id AND fk.parent_column_id = col.column_id LEFT OUTER JOIN ( SELECT parent_column_id , parent_object_id , 'Check' AS check_const FROM sys.check_constraints AS c GROUP BY parent_column_id , parent_object_id ) AS ch ON col.column_id = ch.parent_column_id AND col.object_id = ch.parent_object_id LEFT OUTER JOIN ( SELECT index_columns_1.object_id , index_columns_1.column_id FROM sys.index_columns AS index_columns_1 INNER JOIN sys.indexes AS indexes_1 ON indexes_1.index_id = index_columns_1.index_id AND indexes_1.object_id = index_columns_1.object_id WHERE (indexes_1.is_unique_constraint = 1) GROUP BY index_columns_1.object_id , index_columns_1.column_id ) AS uk ON col.column_id = uk.column_id AND col.object_id = uk.object_id LEFT OUTER JOIN sys.extended_properties AS ep ON tab.object_id = ep.major_id AND col.column_id = ep.minor_id AND ep.name = 'MS_Description' AND ep.class_desc = 'OBJECT_OR_COLUMN' ON cc.object_id = tab.object_id AND cc.column_id = col.column_id
sources : https://dataedo.com/blog/useful-sql-server-data-dictionary-queries-every-dba-should-have
Disable all triggers on a table, you do what you have to do, after enable only the triggers that have been disabled.
Step 1 : Disable all triggers
--#region Disable triggers DECLARE @triggers TABLE (SqlEnableTrigger NVARCHAR(MAX)); DECLARE @SchemaName VARCHAR(128) = 'mySchema'; – ** TO DO ** DECLARE @TableName VARCHAR(128) = 'mytable'; – ** TO DO ** DECLARE @SqlTrigger NVARCHAR(MAX); DELETE FROM @triggers; INSERT INTO @triggers (SqlEnableTrigger) SELECT 'ENABLE TRIGGER ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(tg.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(tb.name) + N';' AS SqlEnableTrigger FROM sys.schemas AS sc INNER JOIN sys.tables AS tb ON sc.schema_id = tb.schema_id RIGHT OUTER JOIN sys.triggers AS tg ON tb.object_id = tg.parent_id WHERE (sc.name =@SchemaName) AND (tb.name = @TableName) AND (tg.is_disabled = 0); SET @SqlTrigger = 'DISABLE TRIGGER ALL ON '+QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName); PRINT @SqlTrigger; EXEC (@SqlTrigger); --#endregion Disable triggers
Step 2: You do what you have to do.
Your code... to update, to delete, to insert, or everything you want.
Step 3 : Enable all triggers that have been disabled.
--#region Enaable triggers SET @SqlTrigger = ''; SELECT @SqlTrigger = @SqlTrigger + SqlEnableTrigger FROM @triggers; PRINT @SqlTrigger; EXEC (@SqlTrigger); --#endregion Enaable triggers
List all triggers, the SchemaName is empty when it is a DDL trigger
SELECT ISNULL(sc.name,'') AS SchemaName, ISNULL(tb.name,'(Database)') AS TableName, tg.name, tg.object_id, tg.parent_class, tg.parent_class_desc, tg.parent_id, tg.type, tg.type_desc, tg.create_date, tg.modify_date, tg.is_ms_shipped, tg.is_disabled, tg.is_not_for_replication, tg.is_instead_of_trigger, OBJECTPROPERTY(tg.object_id, 'ExecIsUpdateTrigger') AS isupdate, OBJECTPROPERTY(tg.object_id, 'ExecIsDeleteTrigger') AS isdelete, OBJECTPROPERTY(tg.object_id, 'ExecIsInsertTrigger') AS isinsert, OBJECTPROPERTY(tg.object_id, 'ExecIsAfterTrigger') AS isafter, OBJECTPROPERTY(tg.object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof FROM sys.schemas AS sc INNER JOIN sys.tables AS tb ON sc.schema_id = tb.schema_id RIGHT OUTER JOIN sys.triggers AS tg ON tb.object_id = tg.parent_id;
A jobSql Job was changed. The questions are: When? by Who?
We suppose you always have a copy of yours jobs.
** Check the SQL Script for shared folder, your e-mail and SMTP server.
Delete old audit configuration – make sure you want to do that.
print @@servername USE [master] GO BEGIN TRY ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification] WITH (STATE = OFF); END TRY BEGIN CATCH END CATCH; BEGIN TRY DROP SERVER AUDIT SPECIFICATION [ServerAuditSpecification]; END TRY BEGIN CATCH END CATCH; GO BEGIN TRY ALTER SERVER AUDIT [ServerAudit] WITH (STATE = OFF); END TRY BEGIN CATCH END CATCH; BEGIN TRY DROP SERVER AUDIT [ServerAudit]; END TRY BEGIN CATCH END CATCH; GO --xp_cmdshell 'DIR L:Audit*' exec xp_cmdshell 'MD C:Audit*'; exec xp_cmdshell 'DEL C:Audit*.sqlaudit'; GO
Create an Audit on a network share
PRINT '-- Step 1 - Create an Audit on a network share' USE [master]; GO CREATE SERVER AUDIT [ServerAudit] TO FILE ( FILEPATH = N'C:Audit' ,MAXSIZE = 100 MB ,MAX_FILES = 3500 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ) GO Go PRINT '-- Step 2 - Enable the Audit' ALTER SERVER AUDIT [ServerAudit] WITH (STATE=ON); Go Go -- Step 3 - Create a Server Audit Specification CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification] FOR SERVER AUDIT [ServerAudit] --ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP), --ADD (AUDIT_CHANGE_GROUP), --ADD (BACKUP_RESTORE_GROUP), --ADD (BROKER_LOGIN_GROUP), --ADD (DATABASE_CHANGE_GROUP), --ADD (DATABASE_LOGOUT_GROUP), --ADD (DATABASE_MIRRORING_LOGIN_GROUP), --ADD (DATABASE_OBJECT_ACCESS_GROUP), --ADD (DATABASE_OBJECT_CHANGE_GROUP), --ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP), --ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), --ADD (DATABASE_OPERATION_GROUP), --ADD (DATABASE_OWNERSHIP_CHANGE_GROUP), --ADD (DATABASE_PERMISSION_CHANGE_GROUP), --ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), --ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP), --ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), --ADD (DBCC_GROUP), --ADD (FAILED_DATABASE_AUTHENTICATION_GROUP), --ADD (FAILED_LOGIN_GROUP), --ADD (FULLTEXT_GROUP), --ADD (LOGIN_CHANGE_PASSWORD_GROUP), --ADD (LOGOUT_GROUP), --ADD (SCHEMA_OBJECT_ACCESS_GROUP), --ADD (SCHEMA_OBJECT_CHANGE_GROUP), --ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP), --ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP), --ADD (SERVER_OBJECT_CHANGE_GROUP), --ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP), --ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP), --ADD (SERVER_OPERATION_GROUP), --ADD (SERVER_PERMISSION_CHANGE_GROUP), --ADD (SERVER_PRINCIPAL_CHANGE_GROUP), --ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP), --ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), --ADD (SERVER_STATE_CHANGE_GROUP), --ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP), --ADD (SUCCESSFUL_LOGIN_GROUP), --ADD (TRACE_CHANGE_GROUP), --ADD (USER_CHANGE_PASSWORD_GROUP), ADD (USER_DEFINED_AUDIT_GROUP) WITH (STATE = ON); GO
Log or send e-mail when a job was changed.
PRINT '-- Create/Update trigger [trig_sysjobs_log] ON [dbo].[sysjobs]' USE [msdb] GO BEGIN TRY DROP TRIGGER [dbo].[trig_sysjobs_log]; END TRY BEGIN CATCH END CATCH; SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Description: Audit/Log jobs changements -- ============================================= CREATE TRIGGER [dbo].[trig_sysjobs_log] ON [dbo].[sysjobs] FOR INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON --#region DECLARE variables DECLARE @bodyAudit nvarchar(4000); DECLARE @bodyEvent nvarchar(255); DECLARE @bodyEmail nvarchar(max); DECLARE @subjectEmail nvarchar(255); DECLARE @activity varchar(20) DECLARE @jobName varchar(255); DECLARE @jobNameDeleted varchar(255); SET @JobName = ''; SET @jobNameDeleted = ''; DECLARE @systemUser varchar(100) DECLARE @datelog VARCHAR(50); DECLARE @SUSER_ID varchar(20); DECLARE @originalLogin nvarchar(256); DECLARE @hostname nvarchar(128); DECLARE @programName nvarchar(max); --#endregion DECLARE variables --#region Create message SET @systemUser = SYSTEM_USER; SET @datelog = GETUTCDATE(); SET @SUSER_ID = CAST(SUSER_ID() as varchar(20)); SET @originalLogin = ORIGINAL_LOGIN(); SET @hostname = HOST_NAME(); SET @programName= PROGRAM_NAME(); if exists(SELECT * from inserted) begin if exists (SELECT * from deleted) BEGIN SET @activity = 'UPDATE'; SELECT @jobNameDeleted = name from deleted; END else BEGIN SET @activity = 'INSERT'; END SELECT @jobName = name from inserted; end else begin SET @activity = 'DELETE'; SELECT @jobName = name from deleted; end -- Audit SET @bodyAudit = 'AuditJob' + CHAR(13) + CHAR(10);; SET @bodyAudit = @bodyAudit + N'-Job : ' + @JobName + CHAR(13) + CHAR(10); IF ( @jobNameDeleted NOT IN ( @JobName , '') ) SET @bodyAudit = @bodyAudit + N'-Job Deleted : ' + @jobNameDeleted + CHAR(13) + CHAR(10); SET @bodyAudit = @bodyAudit + N'-Activity : ' + @activity + CHAR(13) + CHAR(10); SET @bodyAudit = @bodyAudit + N'-Original Login : ' + @originalLogin + CHAR(13) + CHAR(10); IF (@originalLogin<>@systemUser) SET @bodyAudit = @bodyAudit + N'-System User : ' + @systemUser + CHAR(13) + CHAR(10); SET @bodyAudit = @bodyAudit + N'-Date UTC : ' + @datelog + CHAR(13) + CHAR(10); SET @bodyAudit = @bodyAudit + N'-Host name : ' + @hostname + CHAR(13) + CHAR(10); SET @bodyAudit = @bodyAudit + N'-SUSER_ID : ' + @SUSER_ID + CHAR(13) + CHAR(10); SET @bodyAudit = @bodyAudit + N'-Program Name : ' + @programName + CHAR(13) + CHAR(10); BEGIN TRY EXEC sp_audit_write @user_defined_event_id = 169 , @succeeded = 0, @user_defined_information = @bodyAudit; END TRY BEGIN CATCH END CATCH; --#endregion Create message --#region Send email -- Check if database mail service is configurated with a default profil IF (EXISTS ( SELECT prinprof.is_default FROM msdb.sys.database_principals dbprin, msdb.dbo.sysmail_principalprofile prinprof, msdb.dbo.sysmail_profile prof WHERE dbprin.principal_id = msdb.dbo.get_principal_id(prinprof.principal_sid) AND prof.profile_id = prinprof.profile_id AND prinprof.is_default = 1 ) ) BEGIN -- Send email BEGIN TRY SET @bodyEmail = @bodyAudit; SET @subjectEmail = @activity+ ' job ['+ @jobName + ']' EXEC msdb.dbo.sp_send_dbmail @recipients = 'Youremail@mydomain.com', @subject = @subjectEmail, @body = @bodyEmail, @from_address = 'noreplay@mydomain.com', @reply_to = 'noreplay@mydomain.com'; END TRY BEGIN CATCH END CATCH; END --#endregion Send email --#region Create Event Log SET @bodyEvent = REPLACE(@bodyAudit, ' ',' '); SET @bodyEvent = REPLACE(@bodyAudit, ' ',' '); SET @bodyEvent = SUBSTRING(@bodyEvent,0, 255); RAISERROR (@bodyEvent, -- Message text. 10, -- Severity, 1 -- State, ) WITH LOG; --#endregion Create Event Log END GO GO
Check your audit
SELECT top 10 * FROM sys.fn_get_audit_file('C:Audit*', NULL, NULL) ORDER BY 1 DESC;
Sources:
you are already in trouble and it is too late when ...
You must have the answer to theses questions:
Growing database files by a percentage is relatively harmless when databases are small.
Once you’ve grown beyond a few gigabytes, a ten percent file growth can be a cause of performance problems while SQL Server has to wait for space to be allocated.
At the other extreme, for databases over a few gigabytes, it doesn’t make sense to keep growing in tiny 1 MB increments where you can run into extreme physical fragmentation.
Generally, it is rather to grow in increments than in percentages.
Script to list database files that use percent growth, you do not need to change, you just need to be sure it’s okay.
SELECT d.name as database_name, mf.name as file_name, mf.type_desc as file_type, mf.growth as current_percent_growth FROM sys.master_files mf (NOLOCK) JOIN sys.databases d (NOLOCK) on mf.database_id=d.database_id WHERE is_percent_growth=1
Script to monitoring size files.
DECLARE @growth_count INT= 3; -- Who many time the database can growth ? SET NOCOUNT ON; --#region Local drives -- DROP TABLE IF EXISTS #svr_drives; CREATE TABLE #svr_drives ( DriveLetter CHAR(1), TotalSpace BIGINT, FreeSpace BIGINT ); -- Populate temp table with free space of all local drives INSERT INTO #svr_drives SELECT DISTINCT SUBSTRING(volume_mount_point, 1, 1) AS DriveLetter, total_bytes / 1024 / 1024 AS TotalSpace, available_bytes / 1024 / 1024 AS FreeSpace FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id); --select * from #svr_drives --#endregion Local drives --#region create sysaltfiles -- DROP TABLE IF EXISTS #sysaltfiles; CREATE TABLE #sysaltfiles ( [dbname] [NVARCHAR](128) NULL, [fileid] [SMALLINT] NULL, [FileSize] [BIGINT] NULL, [TotalSpace] [BIGINT] NULL, [FreeSpace] [BIGINT] NULL, [maxsize] [INT] NOT NULL, [status] [INT] NULL, [dbid] [SMALLINT] NULL, [filename] [NVARCHAR](260) NOT NULL, [Usage] [VARCHAR](4) NOT NULL, [DriveLetter] [NVARCHAR](1) NULL, [IsgrowthInMB] [INT] NOT NULL, [growth] [INT] NOT NULL, [growth_count] [INT], [WillGrowth] [BIGINT] NULL, [WillMissingSpaceDisk] INT, [NearFileSizeLimit] INT, [Message] NVARCHAR(MAX) ); INSERT INTO #sysaltfiles (dbname, fileid, FileSize, TotalSpace, FreeSpace, maxsize, [status], [dbid], [filename], Usage, DriveLetter, IsgrowthInMB, growth ) SELECT DB_NAME(dbid) dbname, s.fileid, (CONVERT(BIGINT, s.[size]) * 8) / 1024 AS FileSize, TotalSpace, FreeSpace AS FreeSpace, maxsize / 128 AS maxsize, [status], [dbid], [filename], CASE STATUS&0x40 WHEN 0x40 THEN 'log' ELSE 'data' END AS 'Usage', SUBSTRING([filename], 1, 1) AS DriveLetter, CASE STATUS&0x100000 WHEN 0x100000 THEN 0 ELSE-1 END AS IsgrowthInMB, growth FROM sys.sysaltfiles s INNER JOIN #svr_drives d ON SUBSTRING([filename], 1, 1) = d.DriveLetter WHERE DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE' AND DATABASEPROPERTYEX(DB_NAME(dbid), 'Updateability') <> 'READ_ONLY' AND DB_NAME(dbid) NOT IN('model'); -- we can add a filter. --#endregion create sysaltfiles --#region Message UPDATE #sysaltfiles SET growth_count = @growth_count, [WillGrowth] = CASE WHEN IsgrowthInMB = 0 THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count) - 1) ELSE(growth / 128) * @growth_count END, WillMissingSpaceDisk = CASE WHEN CASE WHEN IsgrowthInMB = 0 THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count) - 1) ELSE(growth / 128) * @growth_count END > FreeSpace THEN-1 ELSE 0 END, NearFileSizeLimit = CASE WHEN [maxsize] > 0 AND CASE WHEN IsgrowthInMB = 0 THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count)) ELSE FileSize + (growth / 128) * @growth_count END > [maxsize] THEN-1 ELSE 0 END, [Message] = '' FROM #sysaltfiles; UPDATE #sysaltfiles SET [Message] = [Message] + CASE WHEN WillMissingSpaceDisk = -1 THEN 'The database [' + dbname + '] will missing space disk when the file [' + [filename] + '] will growth (' + CAST([WillGrowth] / 1024 AS NVARCHAR(20)) + ' GB) after ' + CAST(growth_count AS NVARCHAR(20)) + ' "growths" on disk (' + [DriveLetter] + ':) ' + CAST([FreeSpace] / 1024 AS NVARCHAR(20)) + ' GB Free of ' + CAST([TotalSpace] / 1024 AS NVARCHAR(20)) + ' GB.' + CHAR(13) + CHAR(10) ELSE '' END; UPDATE #sysaltfiles SET [Message] = [Message] + CASE WHEN NearFileSizeLimit = -1 THEN 'The database [' + dbname + '] is near of file size limit for the file [' + [filename] + ']. Using ' + CAST(FileSize AS NVARCHAR(20)) + ' GB of max ' +CAST(maxsize AS NVARCHAR(20)) + ' GB' + CHAR(13) + CHAR(10) ELSE '' END; DECLARE @Message NVARCHAR(MAX)= ''; SELECT @Message = @Message + [Message] FROM #sysaltfiles WHERE [Message] <> '' ORDER BY dbname; -- SELECT * FROM #sysaltfiles; --#endregion Message PRINT @Message;
If you are using a job to monitoring, you can add this script to send an e-mail.
--#region Send email IF(@Message <> '') BEGIN DECLARE @body NVARCHAR(MAX)= '<p>The database server [servername] will have space disk problem. </p><p/><p/><p>[Message]</p><p/>This message was sent by [JobName] AT [servername]'; DECLARE @jobname SYSNAME, @jobid UNIQUEIDENTIFIER; SELECT @jobname = b.name, @jobid = b.job_id FROM sys.dm_exec_sessions a, msdb.dbo.sysjobs b WHERE a.session_id = @@spid AND (SUBSTRING(MASTER.dbo.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), b.JOB_ID)), 1, 10)) = SUBSTRING(a.PROGRAM_NAME, 30, 10); PRINT 'Job Name : ' + @jobname; SET @body = REPLACE(@body, '[JobName]', @jobname); SET @body = REPLACE(@body, '[servername]', @@SERVERNAME); SET @body = REPLACE(@body, '[Message]', @Message); EXEC msdb.dbo.sp_send_dbmail @recipients = 'user@yourdomain.com', @subject = 'Warning : Monitoring size file database', @body = @body, @body_format = 'HTML', @from_address = 'Sql Server <sender@yourdomain.com>', @reply_to = 'sender@yourdomain.com'; END; --#endregion Send email
Get Job name when you are executing job. This is useful when you need to make reference to the job being executed.
DECLARE @jobname sysname, @jobid uniqueidentifier SELECT @jobname=b.name,@jobid=b.job_id FROM sys.dm_exec_sessions a,msdb.dbo.sysjobs b WHERE a.session_id=@@spid AND (SUBSTRING(MASTER.dbo.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), b.JOB_ID)),1,10)) = SUBSTRING(a.PROGRAM_NAME,30,10) PRINT 'Job Name : ' + @jobname
Instead, you can Use Tokens in Job Steps.
https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps
PRINT 'Job Name : '+'$(ESCAPE_SQUOTE(JOBNAME))' PRINT 'Step Name : '+'$(ESCAPE_SQUOTE(STEPNAME))'