Copy one column to another sqlserver

UPDATE table SET columnB = columnA

–This will update every row.

UPDATE table_name SET
destination_column_name=orig_column_name
WHERE condition_if_necessary

–This will update rows which satisfy the WHERE clause

UPDATE table1
SET column1 = (
SELECT column2
FROM table2
WHERE table2.id = table1.id
);
Whereby:

table1 = table that has the column that needs to be updated
table2 = table that has the column with the data
column1 = blank column that needs the data from column2 (this is in table1)
column2 = column that has the data (that is in table2)

 

–This will update one column based on value of another table column

 

 

 

Advertisements

How to prevent users from accessing SQL Server from any application or any login expect your main application & its login

The SQL Dude!

When a user who isn’t in the logon trigger exception list above tries to connect, they will get this error.

Cannot connect to SERVERNAME.

——————————

ADDITIONAL INFORMATION:

Changed database context to ‘master’.

Changed language setting to us_english.

(Microsoft SQL Server, )

image



image


View original post

List all Highly Fragmented Indexes in SQL Server

IF EXISTS ( SELECT *
FROM [tempdb].[dbo].[sysobjects]
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))
DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details]
GO

CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](
[DatabaseName] [nvarchar] (100) NULL,
[ObjectName] [nvarchar] (100) NULL,
[Index_id] INT,
[indexName] [nvarchar] (100) NULL,
[avg_fragmentation_percent] float NULL,
[IndexType] [nvarchar] (100) NULL,
[Action_Required] [nvarchar] (100) default 'NA'
) ON [PRIMARY]

DECLARE @dbname varchar(1000)
DECLARE @sqlQuery nvarchar(4000)

DECLARE dbcursor CURSOR for
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlQuery = '
USE [' + @dbname + '];

IF EXISTS
(
SELECT compatibility_level
FROM sys.databases
WHERE
name = N'''+ @dbname +'''
AND compatibility_level >= 90
)
BEGIN
INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details]
(
DatabaseName
, ObjectName
, Index_id
, indexName
, avg_fragmentation_percent
, IndexType
)
SELECT
db_name() as DatabaseName
, OBJECT_NAME (a.object_id) as ObjectName
, a.index_id, b.name as IndexName
, avg_fragmentation_in_percent
, index_type_desc
FROM
sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON
a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE
b.index_id <> 0
AND avg_fragmentation_in_percent <> 0
END;'

EXEC sp_executesql @sqlQuery

FETCH NEXT FROM dbcursor
INTO @dbname
END

CLOSE dbcursor
Deallocate dbcursor

-- Update the action require for item with average fragmentation value >30 to "Rebuild"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]
SET Action_Required = 'Rebuild'
WHERE avg_fragmentation_percent >30
GO

-- Update the action require for item with average fragmentation value >5 & <30 to "Reindex"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]
SET Action_Required = 'Reorganize'
WHERE avg_fragmentation_percent <30 and avg_fragmentation_percent >5
GO

-- Show the index fragmentation result
SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details]
ORDER BY databasename

Did You Know?? R Services is in Standard Edition of SQL Server 2016 — The SQL Herald

While my last post extolled the virtues of SQL Server Standard Edition, this week while doing some client testing with Microsoft, I learned about another key standard edition feature. The new SQL Server R services is supported in standard edition of SQL Server 2016. While you won’t get access to some of the cool functions […]

via Did You Know?? R Services is in Standard Edition of SQL Server 2016 — The SQL Herald

Kill All Sessions Coming from a Host in SQL Server

Kill Sessions by HOST SQL Server

–Check no. of sessions coming from all hosts connected to DB —
SELECT hostname, COUNT(hostname) FROM sys.sysprocesses P
JOIN sys.sysdatabases D ON (D.dbid = P.dbid)
JOIN sys.sysusers U ON (P.uid = U.uid)
WHERE hostname != ”
GROUP BY hostname
ORDER BY COUNT(hostname) DESC;

— Create Procedure to kill sessions by Hostname —
CREATE PROCEDURE [dbo].[KillConnectionsHost] @hostname varchar(MAX)
AS
DECLARE @spid int
DECLARE @sql varchar(MAX)

DECLARE cur CURSOR FOR
SELECT spid FROM sys.sysprocesses P
JOIN sys.sysdatabases D ON (D.dbid = P.dbid)
JOIN sys.sysusers U ON (P.uid = U.uid)
WHERE hostname = @hostname AND hostname != ”
AND P.spid != @@SPID

OPEN cur

FETCH NEXT FROM cur
INTO @spid

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONVERT(varchar, @spid)

SET @sql = ‘KILL ‘ + RTRIM(@spid)
PRINT @sql
EXEC(@sql)

FETCH NEXT FROM cur
INTO @spid
END

CLOSE cur
DEALLOCATE cur
— Execute Procedure by passing Hostname as parameter in the Stored Procedure —

exec KillConnectionsHost MSSCOM01