Microsoft SQL Server/Table manipulation
Introduction
editThe DDL and DML respect the SQL-86 norm. However, in addition to the requests SELECT, UPDATE, INSERT
we find MERGE
since the 2008 version[1].
Create a table
editIn SSMS, a right click on the folder "Tables" of a database allows to add one.
A right click on a particular table lets choose between:
- Modify the table structure (add a column, modify a type).
- Select its 1,000 first records (
TOP
), or the 1,000 last (ORDER BY id DESC
). - Edit its 200 first.
Otherwise in SQL one must enter[2]:
CREATE TABLE [dbo].[table1] (
[Nom] [varchar](250) NULL,
[Prénom] [varchar](250) NULL,
[identifiant] [int] IDENTITY(1,1) NOT NULL)
Filling the first columns[3]:
INSERT INTO table1 VALUES ('Doe', 'Jane', 1), ('Doe', 'John', 2)
To aim some other columns, the fields must be precised. For example, by filling the first name, the last name will be null:
INSERT INTO table1 (First_name, id) VALUES ('Jane', 3)
From another table:
INSERT INTO table1 (First_name, id)
SELECT First_name, ID FROM table2
Update:
UPDATE table1
SET First_name = 'Janet'
WHERE ID = 3
UPDATE table1
SET First_name = t2.First_name, Last_name = t2.Last_name
FROM table1 t1
INNER JOIN table2 t2 on t1.ID = t2.ID_t1
Create an index
editThe software PK abbreviation means "primary key".
To create a foreign key, drop down the table, in the menu Keys, right click, new foreign key..., the list of all the table foreign keys appears in a small window (named by default "FK_..." for "foreign key").
In General, Tables and columns specification, click on "..." to select the table and its field to link.
Add a unique id
editNormally each table should own at least one unique id (primary key). However, it's impossible to modify an existing column to attribute the property AUTOINCREMENT
needed to such a key.
So to add one:
ALTER TABLE table1 ADD id int NOT NULL IDENTITY (1,1) PRIMARY KEY
Copy a table
editThe selection below clones a table with the same fields sizes:
SELECT * INTO table2 FROM table1
Knowing that the table spt_values
from the system database master
contains a sequential field number
, it becomes possible to generate tables with this counter:
SELECT DISTINCT number
FROM master.dbo.spt_values
WHERE number BETWEEN 2 AND 10
Where:
SELECT DISTINCT 'Line ' + convert(varchar, number, 112) as N into #BlankTable
FROM master.dbo.spt_values
WHERE number BETWEEN 2 AND 10
SELECT * from #BlankTable
N Line 10 Line 2 Line 3 Line 4 Line 5 Line 6 Line 7 Line 8 Line 9
Import a table
editFrom an array (Excel or Calc) converted for example in CSV encoded in PC DOS, to import it as a new table[4] :
CREATE TABLE Array_to_Table (
[Champ1] [varchar](500) NULL,
[Champ2] [varchar](500) NULL,
[Champ3] [varchar](500) NULL
)
GO
BULK INSERT Array_to_Table
FROM 'C:\Users\superadmin\Desktop\Array1.csv'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
GO
-- Displays the result
SELECT * from Array_to_Table
GO
Delete a table
editTo delete a whole table (data and structure):
DROP TABLE table1
To truncate a table, that is to say to conserve only the headers and columns types, by removing all records:
TRUNCATE TABLE table1
--or
DELETE table1
To delete certain lines from a table:
DELETE table1 WHERE Condition
NB: by adding OUTPUT deleted.*
before the WHERE
, we get the deleted content instead of the deleted lines number.}}
Research a table
editTo research a table which we know the exact name, on all the server databases:
sp_MSforeachdb 'USE ?
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[MyTable]'') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
PRINT ''Table found in the database: ?''
END'
Research in all the tables
editSSMS 10 doesn't propose any research function, like one could find it in phpMyAdmin for MySQL for example.
Table research
editThis script passes through each database to return the tables which names contain the specified string of characters (at the end):
ALTER Proc FindTable
@TableName nVarchar(50)
As
/*
Purpose : Search for a Table in all databases
Author : Sandesh Segu
Date : 17th July 2009
Version : 1.0
More Scripts : http://sanssql.blogspot.com
*/
ALTER Table #temp (DatabaseName varchar(50),SchemaName varchar(50),TableName varchar(50))
Declare @SQL Varchar(500)
Set @SQL='Use [?] ;
if exists(Select name from sys.tables where name like '''+@TableName+''')
insert into #temp
Select ''?'' AS DatabaseName ,SS.Name AS SchemaName ,ST.Name AS TableName from sys.tables as ST , sys.schemas SS
where ST.Schema_ID=SS.Schema_ID and ST.name like '''+@TableName+''''
EXEC sp_msforeachdb @SQL
Select * from #temp
Drop table #temp
GO
/*
Usage: If the exact table name is known then specify the table name else include the wild cards
EXEC FindTable 'Employee'*/
EXEC FindTable '%String of characters to research%'
Research a value
editA field value research in all the tables take a few time[5]:
CREATE TABLE #result(
id INT IDENTITY,
tblName VARCHAR(255),
colName VARCHAR(255),
qtRows INT
)
go
DECLARE @toLookFor VARCHAR(255)
SET @toLookFor = '%String of characters%'
DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
'[' + usr.name + '].[' + tbl.name + ']' AS tblName,
'[' + col.name + ']' AS colName,
LOWER(typ.name) AS typName
FROM
sysobjects tbl
INNER JOIN(
syscolumns col
INNER JOIN systypes typ
ON typ.xtype = col.xtype
)
ON col.id = tbl.id
--
LEFT OUTER JOIN sysusers usr
ON usr.uid = tbl.uid
WHERE tbl.xtype = 'U'
AND LOWER(typ.name) IN(
'char', 'nchar',
'varchar', 'nvarchar',
'text', 'ntext'
)
ORDER BY tbl.name, col.colorder
--
DECLARE @tblName VARCHAR(255)
DECLARE @colName VARCHAR(255)
DECLARE @typName VARCHAR(255)
DECLARE @sql NVARCHAR(4000)
DECLARE @crlf CHAR(2)
SET @crlf = CHAR(13) + CHAR(10)
OPEN cCursor
FETCH cCursor
INTO @tblName, @colName, @typName
WHILE @@fetch_status = 0
BEGIN
IF @typName IN('text', 'ntext')
BEGIN
SET @sql = ''
SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
SET @sql = @sql + 'FROM ' + @tblName + @crlf
SET @sql = @sql + 'WHERE PATINDEX(''%'' + @toLookFor + ''%'', ' + @colName + ') > 0' + @crlf
END
ELSE
BEGIN
SET @sql = ''
SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
SET @sql = @sql + 'FROM ' + @tblName + @crlf
SET @sql = @sql + 'WHERE ' + @colName + ' LIKE ''%'' + @toLookFor + ''%''' + @crlf
END
EXECUTE sp_executesql
@sql,
N'@tblName varchar(255), @colName varchar(255), @toLookFor varchar(255)',
@tblName, @colName, @toLookFor
FETCH cCursor
INTO @tblName, @colName, @typName
END
SELECT *
FROM #result
WHERE qtRows > 0
ORDER BY id
GO
DROP TABLE #result
go
References
edit- ↑ https://msdn.microsoft.com/en-us/library/bb510625.aspx
- ↑ https://msdn.microsoft.com/en-us/library/ms174979.aspx
- ↑ https://msdn.microsoft.com/en-us/library/ms174335.aspx
- ↑ https://msdn.microsoft.com/en-us/library/ms188365.aspx
- ↑ http://stackoverflow.com/questions/591853/search-for-a-string-in-an-all-the-tables-rows-and-columns-of-a-db