Create a sql stement from existing tables

Is there any way to automatically create a "CREATE TABLE" statment using an
existing table?
I would simply want to execute this statement and it would create, in
another database, a table with the same structure (no data).
Is there a way to do this?
cheers
nathan
hi Nathan,
Nathan wrote:
> Is there any way to automatically create a "CREATE TABLE" statment
> using an existing table?
> I would simply want to execute this statement and it would create, in
> another database, a table with the same structure (no data).
> Is there a way to do this?
not directly... you can use tools like Enterlirse Manager (if you are
licensed to) or even free tools like QALite
(http://www.rac4sql.net/qalite_main.asp) to generate thos kind of DDL...
but with some efforts you can dig into the INFORMATION_SCHEMA views to
output the desired result...
start with
http://msdn.microsoft.com/library/de...a-iz_87w3.asp,
INFORMATION_SCHEMA.COLUMNS..
this is a 5 minutes sample... if you like more deep details such as
constraints, keys, index and the like you have to expand your search on the
other INFORMATION_SCHEMA views..
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE dbo.table1 (
Id int NOT NULL PRIMARY KEY ,
vc varchar(10) NULL ,
c char(1) NOT NULL DEFAULT ('a') ,
i int ,
d decimal(18,4) NOT NULL DEFAULT 0
)
GO
DECLARE @.COLUMN_NAME nvarchar(128),
@.COLUMN_DEFAULT nvarchar(4000),
@.IS_NULLABLE varchar(3),
@.DATA_TYPE nvarchar(128),
@.NUMERIC_PRECISION tinyint,
@.NUMERIC_SCALE int,
@.COLLATION_NAME nvarchar(128),
@.CHARACTER_MAXIMUM_LENGTH smallint
DECLARE @.cmd varchar(8000)
SET @.cmd = ''
DECLARE t CURSOR FOR
SELECT c.COLUMN_NAME, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.DATA_TYPE,
c.NUMERIC_PRECISION, c.NUMERIC_SCALE, c.COLLATION_NAME,
c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'table1'
ORDER BY c.ORDINAL_POSITION
OPEN t
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF DATALENGTH(@.cmd) <> 0 SET @.cmd = @.cmd + ' ,' + CHAR(10)
SET @.cmd = @.cmd + CHAR(9) + QUOTENAME( @.COLUMN_NAME )+ ' ' + @.DATA_TYPE + '
'
IF @.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar')
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.CHARACTER_MAXIMUM_LENGTH) + ') '
IF @.DATA_TYPE = 'decimal' OR @.DATA_TYPE = 'numeric'
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.NUMERIC_PRECISION) + ', ' +
CONVERT(varchar, @.NUMERIC_SCALE) + ') '
IF NOT @.COLUMN_DEFAULT IS NULL
SET @.cmd = @.cmd + 'DEFAULT ' + @.COLUMN_DEFAULT + ' '
IF NOT @.COLLATION_NAME IS NULL
SET @.cmd = @.cmd + 'COLLATE ' + @.COLLATION_NAME + ' '
IF @.IS_NULLABLE = 'YES'
SET @.cmd = @.cmd + 'NULL '
ELSE
SET @.cmd = @.cmd + 'NOT NULL '
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
END
CLOSE t
DEALLOCATE t
SET @.cmd = 'CREATE TABLE ' + QUOTENAME('dbo') + '.' + QUOTENAME('table1') +
' (' + CHAR(10)
+ @.cmd + CHAR(10)
+ CHAR(9) + ')'
SELECT @.cmd
GO
DROP TABLE dbo.table1
--<--
CREATE TABLE [dbo].[table1] (
[Id] int NOT NULL ,
[vc] varchar (10) COLLATE Latin1_General_CI_AS NULL ,
[c] char (1) DEFAULT ('a') COLLATE Latin1_General_CI_AS NOT NULL ,
[i] int NULL ,
[d] decimal (18, 4) DEFAULT (0) NOT NULL
)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Create a Condition for a DTS Task

I simply want an email to go out if any records in a table exist, else no email. I have other steps completed in this DTS job but this is the last step. Any ideas?
ddaveNever mind. I decided it be best to drop it into a stored procedure.

ddave

Cpu Usage High.

Hi,
can we check cpu usage of our server by query analyzer,I dont have direct ac
cess to server and simply i've registered it by entr manager.
Site is too slow..there are no locks no processesing..but still its moving l
ike dead..
i want to check by query analyzer..
1) cpu usage
2) buffer cache ratio
3) ram utilised and free on server.
regards
sunnyHi,
The easiest way is that, you can connect to Performace monitor remotely.
Open Performance monitor and click the ADD counter butter,
where you can mention the SQL server name (\\Servername). But you should
have required OS level previlages in the SQL Server machine.
Buffer hit ratio can verified using the below DBCC command
DBCC PERFMON
Note:
Incase if you found all the hardware resouces usage is below limit than
using profiler identify the TSQL/ Procedure causing
the bottle neck. After that try to tune using SQL Serevr Query execution
plan and Index tuning wizard.
Thanks
Hari
MCDBA
"sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:7EDA9B30-F184-40DD-8FEE-FE144D43D9B8@.microsoft.com...
> Hi,
> can we check cpu usage of our server by query analyzer,I dont have direct
access to server and simply i've registered it by entr manager.
> Site is too slow..there are no locks no processesing..but still its moving
like dead..
> i want to check by query analyzer..
> 1) cpu usage
> 2) buffer cache ratio
> 3) ram utilised and free on server.
> regards
> sunny
>|||can use sp_monitor to check cpu activity since sql was started. . .cpu_busy
column tells you how much time cpu has spent servicing sql server requests

Cpu Usage High.

Hi,
can we check cpu usage of our server by query analyzer,I dont have direct access to server and simply i've registered it by entr manager.
Site is too slow..there are no locks no processesing..but still its moving like dead..
i want to check by query analyzer..
1) cpu usage
2) buffer cache ratio
3) ram utilised and free on server.
regards
sunnyHi,
The easiest way is that, you can connect to Performace monitor remotely.
Open Performance monitor and click the ADD counter butter,
where you can mention the SQL server name (\\Servername). But you should
have required OS level previlages in the SQL Server machine.
Buffer hit ratio can verified using the below DBCC command
DBCC PERFMON
Note:
Incase if you found all the hardware resouces usage is below limit than
using profiler identify the TSQL/ Procedure causing
the bottle neck. After that try to tune using SQL Serevr Query execution
plan and Index tuning wizard.
Thanks
Hari
MCDBA
"sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:7EDA9B30-F184-40DD-8FEE-FE144D43D9B8@.microsoft.com...
> Hi,
> can we check cpu usage of our server by query analyzer,I dont have direct
access to server and simply i've registered it by entr manager.
> Site is too slow..there are no locks no processesing..but still its moving
like dead..
> i want to check by query analyzer..
> 1) cpu usage
> 2) buffer cache ratio
> 3) ram utilised and free on server.
> regards
> sunny
>|||can use sp_monitor to check cpu activity since sql was started. . .cpu_busy column tells you how much time cpu has spent servicing sql server requests

counting distint records

i have an sql statement like this:

SELECT distinct store, dept, sku
FROM some_table
WHERE some_condition

i simply want to modify it to give me the count, i tried this, but it doesn't work:

SELECT count (distinct store, dept, sku)
FROM some_table
WHERE some_condition

what am i doing wrong? thank you very much for your help.This is a pure guess, but do you want something like:SELECT Count(*), store, dept, sku
FROM some_table
WHERE 1 = 1 -- or some other condition of your choosing
GROUP BY store, dept, sku-PatP|||thanks for the help, yeah i think that will work, but i ended up doing something like this:

SELECT count(1)
FROM ( SELECT distinct store, dept, sku
FROM some_table
WHERE some_condition
)