PL-SQL Query

Creating Primary Key, Foreign Key and Default Constraint.

Create Table Statement to create Primary Key

a. Column Level

USE AdventureWorks2008

CREATE TABLE Products

(

ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,

ProductName VARCHAR(25)

);

b. Table Level

CREATE TABLE Products

(

ProductID INT,

ProductName VARCHAR(25)

CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)

);

2) Alter Table Statement to create Primary Key

ALTER TABLE Products

ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)

3) Alter Statement to Drop Primary key

ALTER TABLE Products

DROP CONSTRAINT pk_products_pid;

What is the difference between count (column_name) and count (*) in sql server.

The difference is as follows:

Count (*) : Return the count of all columns including null values column also.

Count (column_name): Returns the count of data in the table excluding the null values.

Execute the following statement in SSMS and check the result set:

create table #tab(recordid1 int,recordid2 int)

insert #tab values(null,null)

insert #tab values(1,null)

insert #tab values(null,1)

insert #tab values(1,null)

insert #tab values(null,1)

insert #tab values(1,1)

insert #tab values(null,null)

select count(*),count(recordid1),count(recordid2),count(1)from #tab

Script drop temp table.

CREATE TABLE #Usertemptable (col1 int);

INSERT INTO #Usertemptable VALUES (10);

SELECT * FROM #Usertemptable;

IF OBJECT_ID(N'tempdb..#Usertemptable', N'U') IS NOT NULL

DROP TABLE #Usertemptable;

---To test where the temp table is dropped or not execute the select statment.

---SELECT * FROM #temptable;

SELECT * FROM #Usertemptable;

SQL Script to delete all foreign key constraints in the database

while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))

begin

declare @sql nvarchar(2000)

SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')

FROM information_schema.table_constraints

WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

exec (@sql)

PRINT @sql

end

Important : Care must be taken before executing the above script as after execution it will delete all the foreign key constraint from the database and before execution on any of the critical environment (such as production) the script must be tested and impact should be analyzed on development environment.

T-SQL script to rename database table column name or rename table.

sp_rename 'tablename.oldcolumnname','newcolumnname','COLUMN'

The script for renaming any object (table, sp etc) :

sp_rename '[OldTableName]' , '[NewTableName]'

How to add left pad value in sql server 2008

CREATE TABLE #AllRecords

(

IDS VARCHAR(200)

)

INSERT INTO #AllRecords VALUES(12345)

INSERT INTO #AllRecords VALUES(1234)

INSERT INTO #AllRecords VALUES(123)

INSERT INTO #AllRecords VALUES(12)

INSERT INTO #AllRecords VALUES(1)

--Requirement All the ids should be left padded with 0 if the len of the data in the column is less than 5

SELECT REPLICATE('0', 5 -DATALENGTH(IDS))+ IDS AS NAME FROM #AllRecords

Query to get all default constraints in the database.

select c.name,b.name AS 'Table',a.name AS 'Column'

from sys.all_columns a

inner join sys.tables b on a.object_id = b.object_id

inner join sys.default_constraints c on a.default_object_id = c.object_id

where b.name='tablename' and a.name = 'columnname'

If you want to get all the default constraint in the database execute the above query without where clause. If you need the data for a specific table name or column name then specify the table name and column name in the where clause as given.

Query to find Any text in all stored procedures in the database.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION

LIKE '%INSERT INTO UserMaster%' AND ROUTINE_TYPE='PROCEDURE'

TSQL Script to Get Hard Drives Details

Note: Before executing script, don't forget to enable Ole Automation Procedures from Surface Area Configuration or using sp_configure.

EXEC sp_configure 'Ole Automation Procedures'; GO

The following example shows how to enable OLE Automation procedures.

sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO

SET NOCOUNT ON

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')

DROP TABLE ##_DriveSpace

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')

DROP TABLE ##_DriveInfo

DECLARE @Result INT

, @objFSO INT

, @Drv INT

, @cDrive VARCHAR(13)

, @Size VARCHAR(50)

, @Free VARCHAR(50)

, @Label varchar(10)

CREATE TABLE ##_DriveSpace

(

DriveLetter CHAR(1) not null

, FreeSpace VARCHAR(10) not null

)

CREATE TABLE ##_DriveInfo

(

DriveLetter CHAR(1)

, TotalSpace bigint

, FreeSpace bigint

, Label varchar(10)

)

INSERT INTO ##_DriveSpace

EXEC master.dbo.xp_fixeddrives

-- Iterate through drive letters.

DECLARE curDriveLetters CURSOR

FOR SELECT driveletter FROM ##_DriveSpace

DECLARE @DriveLetter char(1)

OPEN curDriveLetters

FETCH NEXT FROM curDriveLetters INTO @DriveLetter

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

SET @cDrive = 'GetDrive("' + @DriveLetter + '")'

EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT

IF @Result = 0

EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT

IF @Result <> 0

EXEC sp_OADestroy @Drv

EXEC sp_OADestroy @objFSO

SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )

SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )

INSERT INTO ##_DriveInfo

VALUES (@DriveLetter, @Size, @Free, @Label)

END

FETCH NEXT FROM curDriveLetters INTO @DriveLetter

END

CLOSE curDriveLetters

DEALLOCATE curDriveLetters

PRINT 'Drive information for server ' + @@SERVERNAME + '.'

PRINT ''

-- Produce report.

SELECT DriveLetter

, Label

, FreeSpace AS [FreeSpace MB]

, (TotalSpace - FreeSpace) AS [UsedSpace MB]

, TotalSpace AS [TotalSpace MB]

, ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]

FROM ##_DriveInfo

ORDER BY [DriveLetter] ASC

DROP TABLE ##_DriveSpace

DROP TABLE ##_DriveInfo

SHRINK FILE SCRIPT --ONLY CHANGE THE FIELD_ID NUMBER like 1for mdf &2 ldf

DECLARE @Log_name VARCHAR(1000)

DECLARE @Db_name VARCHAR(1000)

DECLARE @Recovery_model_desc VARCHAR(1000)

DECLARE @SQL nvarchar(2000)

DECLARE db_cursor CURSOR FOR

SELECT F.NAME AS [LOG_NAME], DB.NAME AS [DB_NAME], DB.RECOVERY_MODEL_DESC AS [RECOVERY_MODEL_DESC]

FROM MASTER.SYS.MASTER_FILES F INNER JOIN MASTER.SYS.DATABASES DB ON DB.DATABASE_ID = F.DATABASE_ID

WHERE F.FILE_ID=2 AND DB.NAME <> 'tempdb'

OPEN db_cursor FETCH NEXT

FROM db_cursor INTO @Log_name, @Db_name, @Recovery_model_desc WHILE @@FETCH_STATUS = 0

BEGIN

--SET @SQL = N' ALTER DATABASE '+ @Db_name + N' SET RECOVERY SIMPLE WITH NO_WAIT SELECT F.SIZE FROM MASTER.SYS.MASTER_FILES F INNER JOIN MASTER.SYS.DATABASES DB ON DB.DATABASE_ID = F.DATABASE_ID WHERE F.NAME = ''' + @Log_name +''' AND DB.NAME = ''' + @Db_name+''' '

SET @SQL = ' USE [' + @Db_name + '] DBCC SHRINKFILE (' + @Log_name + ', 10)';

EXECUTE sp_executesql @SQL ;

--print @SQL

FETCH NEXT FROM db_cursor INTO @Log_name, @Db_name, @Recovery_model_desc

END

CLOSE db_cursor

DEALLOCATE db_cursor

*******************************************************************************************************************************************************************************************

--Find the roll access information against the DataBASE SCRIPT .

---Execute this command in MSDB ----

declare @targetdb varchar(256),

@sourcedb varchar(256),

@sourceinstance varchar(256),

@postrestoreproc varchar(256)

set @targetdb = 'wellvalu'

set @postrestoreproc = ''

--Copied from EMDC on Nov'11 (Sumit)

--Changed permissions part completely on Dec'12 (Sumit)

SET NOCOUNT ON

DECLARE @Count int

DECLARE @SQL varchar(1000)

DECLARE @SourceDirectory varchar(1000)

DECLARE @Filename varchar(500)

DECLARE @ERR int

DECLARE @environ nvarchar(50)

if @targetdb = db_name()

begin

print 'This proc can not be executed from within the @targetdb'

print '1 '

end

CREATE TABLE #SQLGrants (ID int identity (1,1), SQL varchar(1000))

--New Code--

set @targetdb = '[' + replace(replace(@targetdb, '[', ''), ']','')+ ']'

create table #Permissions (Owner sysname, Object sysname, Grantee sysname

, Grantor sysname, ProtectType sysname, Action sysname, ColumnName sysname)

set @SQL = 'insert into #Permissions exec '+ @targetdb + '..sp_helprotect'

exec (@SQL)

--Creating custom Roles, if exists...

set @SQL = 'insert into #SQLGrants (SQL) ' +

'select ''use ' + @targetdb + ';' + char(13) +

'if not exists (select * from ' + @targetdb + '..sysusers where name = '' + char(39) + name + char(39) + '')'

+ char(13) +

'CREATE ROLE ['' + name + ''] AUTHORIZATION [dbo]''' +

' from ' + @targetdb + '..sysusers where (issqlrole = 1 OR isapprole = 1) and name not in (' +

'''public'',' +

'''db_owner'',' + '''db_accessadmin'',' + '''db_securityadmin'',' + '''db_ddladmin'',' + '''db_backupoperator'',' +

'''db_datareader'',' + '''db_datawriter'',' + '''db_denydatareader'',' + '''db_denydatawriter''' + ')'

exec (@SQL)

select @err=@@error

if @err <> 0

begin

raiserror('Error: can not access targetdb %s',10,1,@targetdb)

print @err

end

--Creating users and assigning permissions to custom roles...

insert into #SQLGrants (SQL)

select

Case Action

when 'CONNECT' then

case l.isntgroup

when 0 then

'use ' + @targetdb + ';' + char(13) + 'if not exists (select * from ' + @targetdb + '..sysusers where name=N' +

char(39) + Grantee + char(39) + ' and hasdbaccess=1) ' + char(13) + 'begin ' + char(13) + 'use '+ @targetdb + ';' + char(13) + 'Create User ' + '[' + Grantee + ']' + ' for login ' +

'[' + Grantee + ']' + ' with default_schema = ' + '[' + Grantor + '];' + char(13) + 'end ; ' + char(13) +

'use ' + @targetdb + '; ' + char(13) + ProtectType + ' ' + Action + ' to [' + Grantee + ']' + char(13)

else

'use ' + @targetdb + ';' + char(13) + 'if not exists (select * from ' + @targetdb + '..sysusers where name=N' +

char(39) + Grantee + char(39) + ' and hasdbaccess=1) ' + char(13) + 'Begin ' + char(13) + 'use '+ @targetdb + ';' + char(13) + 'Create User ' + '[' + Grantee + ']' + ' for login ' +

'[' + Grantee + '];end; ' + char(13) +

'use ' + @targetdb + '; ' + char(13) + ProtectType + ' ' + Action + ' to [' + Grantee + ']'

end

Else

'use ' + @targetdb + '; ' + char(13) + ProtectType + ' ' + Action + ' to [' + Grantee + ']'

End as Command

from #Permissions P

left outer join master..syslogins l on P.Grantee = l.Name

where Owner ='.' and Object ='.'

--assigning roles to users..

select @SQL='INSERT INTO #SQLGrants (SQL) SELECT ''exec '+@targetdb+'..sp_addrolemember '''''' + sr.name + '''''',''''''+su.name+'''''''' '+

'from '+@targetdb+'..sysmembers sm join '+@targetdb+'..sysusers su on sm.memberuid=su.uid join '+@targetdb+'..sysusers sr on sm.groupuid=sr.uid where su.name <> ''dbo'''

exec (@SQL)

select @err=@@error

if @err <> 0

begin

raiserror('Error: can not access targetdb %s',10,1,@targetdb)

print @err

end

--Mapping users...

select @SQL='INSERT INTO #SQLGrants (SQL) select ''exec '+@targetdb+'..sp_change_users_login ''''auto_fix'''',''''''+a.name+'''''''' from '+@targetdb+'..sysusers a join master..syslogins b ' +

'on (a.name=b.name collate latin1_general_ci_as) where issqluser=1'

exec (@SQL)

select @err=@@error

if @err <> 0

begin

raiserror('Error: can not access targetdb %s',10,1,@targetdb)

print @err

end

--Assigning Object level permissions...

insert into #SQLGrants (SQL)

select 'use '+ @targetdb + '; ' + char(13) + ProtectType + ' ' + Action + ' on [' + Owner + '].[' + Object + '] to [' + Grantee + ']'

from #Permissions where Grantee <> 'public'

and Grantee not in

(select Grantee from #Permissions where Owner ='.' and Object ='.' and action <> 'CONNECT')

and Owner <> '.' and Object <> '.'

drop table #Permissions

if not exists (select * from msdb.dbo.sysobjects where id = object_id(N'[msdb].[dbo].[DevDBPermissions]') and type='U')

begin

CREATE TABLE [msdb].[dbo].[DevDBPermissions] (

[dbname] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL ,

[permission] [nvarchar] (512) COLLATE Latin1_General_CI_AS NOT NULL

) ON [PRIMARY]

CREATE CLUSTERED INDEX [IDXDevDBPermissions] ON [msdb].[dbo].[DevDBPermissions]([dbname]) ON [PRIMARY]

end

set @targetdb = replace(replace(@targetdb, '[', ''), ']','')

begin tran

if (select count(*) from #SQLGrants) <> 0

begin

delete msdb.dbo.DevDBPermissions where dbname=@targetdb

select @err=@@error

if @err <> 0

begin

raiserror('Error: can not delete from msdb.dbo.DevDBPermissions',10,1)

rollback tran

print @err

end

insert msdb.dbo.DevDBPermissions (dbname,permission)

select @targetdb,SQL from #SQLGrants order by ID

select @err=@@error

if @err <> 0

begin

raiserror('Error: can not insert into msdb.dbo.DevDBPermissions',10,1)

rollback tran

print @err

end

commit tran

select * from msdb.dbo.DevDBPermissions where dbname = 'wellvalu' and permission like '%taxuser%'

*******************************************************************************************************************************************************************************************