Create Table Statement to create Primary Key
a. Column Level
USE AdventureWorks2008
GO
CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,
ProductName VARCHAR(25)
);
GO
b. Table Level
CREATE TABLE Products
(
ProductID INT,
ProductName VARCHAR(25)
CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
);
GO
2) Alter Table Statement to create Primary Key
ALTER TABLE Products
ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
GO
3) Alter Statement to Drop Primary key
ALTER TABLE Products
DROP CONSTRAINT pk_products_pid;
GO
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
CREATE TABLE #Usertemptable (col1 int);
GO
INSERT INTO #Usertemptable VALUES (10);
GO
SELECT * FROM #Usertemptable;
GO
IF OBJECT_ID(N'tempdb..#Usertemptable', N'U') IS NOT NULL
DROP TABLE #Usertemptable;
GO
---To test where the temp table is dropped or not execute the select statment.
---SELECT * FROM #temptable;
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
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'
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
GO
DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo
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
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%'
*******************************************************************************************************************************************************************************************