How to change schema of all tables, views and stored procedures in MSSQL

Yes, it is possible.
To change the schema of a database object you need to run the following SQL script:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName

Where ObjectName can be the name of a table, a view or a stored procedure. The problem seems to be getting the list of all database objects with a given shcema name. Thankfully, there is a system table named sys.Objects that stores all database objects. The following query will generate all needed SQL scripts to complete this task:

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
AND (DbObjects.Type IN ('U', 'P', 'V'))

Where type 'U' denotes user tables, 'V' denotes views and 'P' denotes stored procedures.

Now you can run all these generated queries to complete the transfer operation.

Reference : http://stackoverflow.com/questions/17571233/how-to-change-schema-of-all-tables-views-and-stored-procedures-in-mssql

How to make a copy of a existing database into new database

This feature is not available in Express version.

Right Click on database -> Task -> Copy Database.

It will open a wizard you have to follow, where you will have to select the source database and enter a new database name to copy whole database objects into the newer one.

SQL statement which will give me all the days of a given month as individual rows

Hi friends,

I have searched a lot in Google and finally I found that its too easy to get the result. Check the query given below.

DECLARE @month    INT
DECLARE @year    INT
SET @month=2
SET @year = 2016

SELECT CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number 'date'
FROM master..spt_values WHERE type = 'P'
AND
(CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) )

 

Last modified Tables & Stored Procedures details in Microsoft SQL Server

Let's find out the last modified Tables & Stored Procedures details in MSSQL by writting a simple Query.

For User Tables

SELECT * FROM sys.objects WHERE type='U' ORDER BY modify_date DESC

For Stored Procedures

SELECT * FROM sys.objects WHERE type='P' ORDER BY modify_date DESC


These Queries fetching records from system object table order by last modified date & time.