Tag: MSSQL

  • SQL Managemnet Studio (SSMS)- Retrieve Unsaved Scripts

    While working with SQL Server Management Studio (SSMS), we can have crash issues. This causes loss of our scripts / queries / query windows data. Sometimes we get “Not responding” error message and restarted again after automatically closed. We can’t stop this. This will be frustrating to re-write all statements again. So, there are some…

  • How to list SQL Jobs using Query or SSMS?

  • How to update the Max Memory at SQL Server?

    In MS SQL Server, update the maximum server memory configuration using either SQL Server Management Studio (SSMS) Explore or T-SQL query. Methods are listed below:

  • How to use CLR function at SQL Server?

    Creating a CLR function in SQL Server involves the following steps: 1. Define the function as a static method of a class in a language supported by the .NET Framework 2. Register the assembly in SQL Server by using the CREATE ASSEMBLY statement 3. Create the function that references the registered assembly by using the…

  • How to rename column at MSSQL

    Use sp_RENAME EXEC sp_RENAME ‘TableName.OldColumnName’ , ‘NewColumnName’, ‘COLUMN’

  • Import CSV using “Microsoft Access Database Engine 2010” to SQL Server

    [SQL] sp_configure ‘Ad Hoc Distributed Queries’, 1 reconfigure with override SET NOCOUNT ON EXEC master..xp_cmdshell ‘del C:\Temp\Schema.ini’ EXEC master..xp_cmdshell ‘echo [Test.txt] > C:\Temp\Schema.ini’ EXEC master..xp_cmdshell ‘echo ColNameHeader=True >> C:\Temp\Schema.ini’ EXEC master..xp_cmdshell ‘echo Format=Delimited(^|) >> C:\Temp\Schema.ini’ EXEC master..xp_cmdshell ‘echo MaxScanRows=50 >> C:\Temp\Schema.ini’ EXEC master..xp_cmdshell ‘echo CharacterSet=OEM >> C:\Temp\Schema.ini’ EXEC master..xp_cmdshell ‘echo Col1=ID TEXT >> C:\Temp\Schema.ini’ EXEC…

  • How to Backup Microsoft SQL Server (MSSQL) Databases

    –1. Variable declaration DECLARE @path VARCHAR(500) DECLARE @name VARCHAR(500) DECLARE @filename VARCHAR(256) — 2. Setting the backup path SET @path = ‘X:\MSSQLDB\Backup\’ DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’) — ignore system databases –3. Initializing cursor operations OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0…

  • Get rows count of the tables – Microsoft SQL Server

    [sql] CREATE TABLE #counts ( table_name varchar(255), row_count int ) EXEC sp_MSForEachTable @command1=’INSERT #counts (table_name, row_count) SELECT ”?”, COUNT(*) FROM ?’ SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC DROP TABLE #counts [/sql]