Problem
While working with the Results to Text option in SSMS, you may come across a situation where the output from dynamically generated data is truncated. In this article I will guide you on how to fix this issue and print all the text for the Results to Text option.
There will be situation while creating the database diagram, you got the below error message.
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
Above error is self explanatory where it is stating that the “set the database owner to valid login” To fix the issue please follow below one of the solution.
Solution 1:
Execute the below script by mentioning the database name.
ALTER AUTHORIZATION ON DATABASE::MentionDatabaseName TO sa GO
OR, you can change the owner by executing the below query.
EXEC sp_changedbowner 'sa'
Solution 2:
Right Click on the database -> Database Properties -> click on files page -> change the owner to SA
Recently I encountered a situation where i have to find out the database file size on SQL Server 2000. Here is the script which you can use to run on SQL Server 2000. Below script will on SQL Server 2005 or SQL Server 2008 as well.
You can use sys.master_files instead of sysAltfiles in SQL Server 2005 and SQL Server 2008. Please find all the different scripts below.
--SQL Server 2000 select fileID ,name ,filename ,(size*8)/1024 SizeMB ,((size*8)/1024)/1024 SizeGB ,db_name(dbid) as DBName from sysaltfiles where db_name(dbid) = 'master' --SQL Server 2000 select fileID ,name ,filename ,(size*8)/1024 SizeMB ,((size*8)/1024)/1024 SizeGB ,db_name(dbid) as DBName from sysaltfiles where filename like '%.ldf%' --SQL Server 2005/2008 SELECT DB_NAME(database_id) AS DBName, Name AS LogicalName, Physical_Name, (size*8)/1024 SizeMB Physical_Name, ((size*8)/1024)/1024 SizeGB FROM sys.master_files WHERE DB_NAME(database_id) = 'Master' GO
In some cases there may be a need to make a SQL Server table read only. There are several different options for doing this and in this tip we cover various ways that you can make a table read only in a SQL Server database.
Compound Primary key is a primary key which is created on more than one column. Now the questions is how to create the foreign key for the compound primary key where it references more than one column.
Check the below example.
create table employee
(
empID int not null,
SSN int not null,
name varchar(20)
)
ALTER TABLE [employee]
ADD CONSTRAINT pk_employee PRIMARY KEY (empID, SSN)
create table EmpDetail
(
empID int,
SSN int,
address varchar(20),
city varchar(20),
pin varchar(20)
)
ALTER TABLE dbo.empDetail
ADD CONSTRAINT FK_Employee
FOREIGN KEY(empID, SSN)
REFERENCES dbo.employee(empID, SSN)
SELECT
tc.TABLE_NAME,
tc.CONSTRAINT_NAME,
ccu.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE
tc.TABLE_NAME IN ('employee','employeeDetail')
You can use the below script to create the Primary Key on the already existing tables. Primary key enforces a uniqueness in the column and created the clustered index as default.
Primary key will not allow NULL values.
-- Adding the NON NULL constraint ALTER TABLE [TableName] ALTER COLUMN PK_ColumnName int NOT NULL --Script to add the primary key on the existing table ALTER TABLE [TableName] ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (PK_ColumnName)
If you want to define or create the non-clustered index on the existing table, you can use the below script. If the data in the column is unique, you can create the Unique Constraint as well.
Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.
--script to create non-clustered Index create index IX_ColumName on TableName(ColumnName) --script to create Unique constraint on the existing table ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE(ColumnName)
In case you have configured SQL Instance to use the static TCP/IP port number. You can connect SQL Server as below using SSMS.
