Saturday, 16 April 2016

SQL Server Core Queries

Rename SQL Server Table Name


Single Rename of table

You can alter / rename table with using stored procedure "sp_rename"

sp_rename 'attachment_factory', 'config_attachment_factory';

Multiple Table Renaming

Use exec with in statement or GO after semicolon (;)
exec sp_rename 'cultures', 'localization_cultures';
exec sp_rename 'office.cashiers', 'office_cashiers';


Get All Schema and User Created Tables


SELECT table_schema,table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'


Operational work of schema dropping 

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
  ALTER TABLE ' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + N' DROP CONSTRAINT '
  + QUOTENAME(c.name) + ';'
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s 
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN ('D','C','F','PK','UQ')
ORDER BY c.[type];

PRINT @sql;