Archive

Posts Tagged ‘schema name’

SQL Server – How to Rename Table/Column in SQL Server

04.03.2013 1 comment

To rename an object in SQL Server you can use sp_rename system stored procedure or you can do this via Object Explorer in SQL Server Management Studio.

Method 1: To rename an object using SQL Server Management Studio:

Step 1. Right Click on the object in Object Explorer and select "Rename"

image

Step 2. Specify new name and press Enter.

image

You can rename any object using object in Object Explorer.

 

Method 2: Renaming an object using sp_rename:

You can also rename using sp_rename system stored procedure.

sp_rename takes below arguments:

Parameter Description
@objname Object Name. When renaming a column you need to specify table name.column name optionally you can also prefix schema name
@newname New name for the specified object
@objtype Type of the object. You can rename below objects using sp_rename:
COLUMN
DATABASE
INDEX
OBJECT
STATISTICS
USERDATATYPE
Default value for this parameter is TABLE

Following example demonstrates how you can use sp_rename to rename table and column names:

Let’s create a Test Table to work with:

USE [SqlAndMe]

GO

 

CREATE TABLE tblProduct

(     

       ID     INT,

       pName  NVARCHAR(50)

)

GO

Now we can rename the Table Name and column names as below:

— Rename table tblProduct to Table_Products

EXEC sp_rename 'tblProduct', 'Table_Products'

GO

 

— Rename Column ID to ProductID

EXEC sp_rename 'Table_Products.ID', 'ProductID', 'COLUMN'

GO

 

— Rename Column pName to ProductName

EXEC sp_rename 'Table_Products.pName', 'ProductName', 'COLUMN'

GO

Result Set:

Caution: Changing any part of an object name could break scripts and stored procedures.

Caution: Changing any part of an object name could break scripts and stored procedures.

Caution: Changing any part of an object name could break scripts and stored procedures.

You can verify that the changes have been made by issuing a SELECT against the table using new table name. SQL Server is also nice enough to warn us that renaming an object can break scripts and stored procedures. What this means is if you have used table/column names in any script/stored procedure it will not be updated automatically, you will need to go through your scripts/stored procedures manually and update them accordingly.

Hope This Helps!

Vishal

SQL Server – Row count for all views / tables

28.12.2011 2 comments

Getting row count for all tables in a database is straight forward. You can display row count for all tables by joining sys.objects and sys.partitions as below:

[UPDATE: sys.partitions only shows an approximation of the number of rows. (http://msdn.microsoft.com/en-us/library/ms175012.aspx)%5D

USE   [AdventureWorks2008R2]

GO

 

SELECT      SCHEMA_NAME(A.schema_id) + '.' +

            A.Name, SUM(B.rows) AS 'RowCount'

FROM        sys.objects A

INNER JOIN sys.partitions B ON A.object_id = B.object_id

WHERE       A.type = 'U'

GROUP BY    A.schema_id, A.Name

GO

Result Set:

Person.Address                    78456

Person.AddressType                18

dbo.AWBuildVersion                1

dbo.BCPTest                       5

Production.BillOfMaterials        8037

Person.BusinessEntity             41554

Person.BusinessEntityAddress      78456

However, for views row count is not available in sys.partitions. To get the row count for a view; you must query the view itself.

USE   [AdventureWorks2008R2]

GO

 

SELECT COUNT(*) FROM HumanResources.vEmployee

GO

Result Set:

290

 

(1 row(s) affected)

This can be encapsulated in a stored procedure to query all available views and then display the result set. The procedure can be created as:

USE   [AdventureWorks2008R2]

GO

 

CREATE PROCEDURE dbo.ViewsRowCount

AS

BEGIN
SET NOCOUNT ON

CREATE TABLE #tempRowCount

(

      Name        VARCHAR(100),

      Row_Count   INT

)

 

DECLARE     @SQL VARCHAR(MAX)

SET         @SQL = ''

SELECT @SQL = @SQL + 'INSERT INTO #tempRowCount SELECT ''' +

            SCHEMA_NAME(schema_id) + '.' + name + ''', COUNT(*) FROM ' +

            SCHEMA_NAME(schema_id) + '.' + name +

            CHAR(13) FROM sys.objects WHERE type = 'V'

EXEC (@SQL)

 

SELECT      Name, Row_Count

FROM        #tempRowCount

END

GO

Once created this stored procedure returns row count for all views in database as bellow:

USE   [AdventureWorks2008R2]

GO

 

EXEC  dbo.ViewsRowCount

GO

Result Set:

Name                           Row_Count

dbo.vApplicationSpecialists    3

Person.vAdditionalContactInfo  10

HumanResources.vEmployee       290

Sales.vIndividualCustomer      18508

Sales.vPersonDemographics      19972

HumanResources.vJobCandidate   13

Hope This Helps!

Vishal