Archive for the ‘MSSQL’ Category

Scripts to Drop/Recreate All FK in a MSSQL Database

    SELECT U.CONSTRAINT_NAME, U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = ‘FOREIGN KEY’ UPDATE @table SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA, PrimaryKeyConstraintTableName = TABLE_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS […]

How to find MSSQL Server Port

DECLARE @tcp_port nvarchar(5) EXEC xp_regread @rootkey = ‘HKEY_LOCAL_MACHINE’, @key = ‘SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP’, @value_name = ‘TcpPort’, @value = @tcp_port OUTPUT select @tcp_port

How to backup all databases from a MSSQL Server to a specific location

The below SQL will provide the backup database instructions needed. It is working on MSSQL 2000 – > 2008 Modify the @PATH variable as needed. declare @PATH VARCHAR(1000) SELECT @PATH=’\\server_name\database_backup\’ DECLARE @SRVNAME VARCHAR(100) SELECT @SRVNAME=COALESCe(@@SERVERNAME, ‘server_not_defined’) select ‘backup database ‘ + name + ‘ to disk=”’ + @path + @SRVNAME + ‘_’ + name + […]

MSSQL 2000: How to get space used, row count and other table specific informations

/********************************************************************************************************************** Purpose: Returns a single result set similar to sp_Space used for all user tables at once. Notes: 1. May be used as a view, stored procedure, or table-valued funtion. 2. Must comment out 1 “Schema” in the SELECT list below prior to use. See the adjacent comments for more info. Revision History: Rev 00 […]

escort kutahya escort bornova escort beylikduzu escort amasya escort diyarbakir