January 5, 2009 at 3:02 pm
I want to create a text file summary of the schematics for a database. Essentially, I want to have what is output for sp_help objectname, but for every object in the database. I can generate the script for the entire database, and that might work, but I don't really want a SQL script, just a summary of the schema (table/object names, field and column names, and datatypes and lengths. Keys and indexes would be nice, too).
A diagram isn't really what I'm after, either, as I think that would be unwieldy, plus I don't really want to have to drag and arrange every single table image in the diagram to get it to look right. I'm thinking a text file would be best.
I thought maybe I could create a cursor and loop through the objects or something, but don't really know what/how the output of running sp_help objectname would work - can I output to text somehow?
Maybe there's another tool or script that would do what I want? Thanks for any info or help!
January 5, 2009 at 5:05 pm
This should do it:
Declare @sql Nvarchar(MAX)
Set @sql = N''
Select @sql = @sql + N'
EXEC sp_help ''['+TABLE_SCHEMA+N'].['+TABLE_NAME+N']'';'
From INFORMATION_SCHEMA.TABLES
Print N'Executing:
'+@sql+'
'
EXEC(@sql)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 10:20 pm
Pete T (1/5/2009)
I want to create a text file summary of the schematics for a database. Essentially, I want to have what is output for sp_help objectname, but for every object in the database. I can generate the script for the entire database, and that might work, but I don't really want a SQL script, just a summary of the schema (table/object names, field and column names, and datatypes and lengths. Keys and indexes would be nice, too).A diagram isn't really what I'm after, either, as I think that would be unwieldy, plus I don't really want to have to drag and arrange every single table image in the diagram to get it to look right. I'm thinking a text file would be best.
I thought maybe I could create a cursor and loop through the objects or something, but don't really know what/how the output of running sp_help objectname would work - can I output to text somehow?
Maybe there's another tool or script that would do what I want? Thanks for any info or help!
use master
go
select * into table_help from information_schema.columns
GO
DECLARE @sql varchar(8000)
DECLARE @BAK_PATH VARCHAR(4000)
declare c_bak cursor FAST_FORWARD FOR
select name from sys.databases where name <> 'master'
OPEN c_bak
FETCH NEXT FROM c_bak INTO @BAK_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'use '+@BAK_PATH+';
insert into table_help select * from information_schema.tables;'
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_bak INTO @BAK_PATH
END
CLOSE C_BAK
DEALLOCATE C_BAK
select * from table_help
GO
January 5, 2009 at 10:36 pm
Yeah, but that uses a cursor. Better to avoid them.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 10:43 pm
RBarryYoung (1/5/2009)
This should do it:
Declare @sql Nvarchar(MAX)
Set @sql = N''
Select @sql = @sql + N'
EXEC sp_help ''['+TABLE_SCHEMA+N'].['+TABLE_NAME+N']'';'
From INFORMATION_SCHEMA.TABLES
Print N'Executing:
'+@sql+'
'
EXEC(@sql)
🙂
"Keep Trying"
January 5, 2009 at 11:00 pm
Make use of Un Documented
sp_MSForEachDB
and
sp_MSForEachTable
EXEC sp_MSForeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.TABLES'
The ? is a way to represent the name of the database. This returns multiple result sets, but is the best you are going to get out of the box. Mainly, there is no consolidated metadata for the server. Each database hosts its own metadata.
January 5, 2009 at 11:05 pm
The undocumented procedures also use cursors.
Also, please note that the request was to document a dsingle database, not every database on the server at once.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 6, 2009 at 7:44 am
Interesting - thanks for all the replies! I'll play with it some more today and see what happens!
May 16, 2012 at 1:52 pm
Is there anyway that you could capture the output of sp_help <table_name> to some table. You know what I mean ?
May 16, 2012 at 2:30 pm
That's really a different question. Best to post it separately in its own thread.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 6, 2013 at 12:43 pm
Hi,
This is a nice script but how can i set up as daily job and capture the output in a file?
Declare @sql Nvarchar(MAX)
Set @sql = N''
Select @sql = @sql + N'
EXEC sp_help ''['+TABLE_SCHEMA+N'].['+TABLE_NAME+N']'';'
From INFORMATION_SCHEMA.TABLES
Print N'Executing:
'+@sql+'
'
EXEC(@sql)
February 8, 2013 at 4:46 am
plz check this query it will be helpfull
select 'exec sp_help' + name from sys.objects where type_desc = 'User_table'
copy the output and then run it @one shot
February 8, 2013 at 9:12 am
Create a table with the same structure as the output from sp_help (e.g. tblHelp) and modify your code to insert to it, e.g.
Select @sql = @sql + N'
INSERT INTO tblHelp EXEC sp_help ''['+TABLE_SCHEMA+N'].['+TABLE_NAME+N']'';'
From INFORMATION_SCHEMA.TABLES
February 8, 2013 at 11:53 am
Richard Warr (2/8/2013)
Create a table with the same structure as the output from sp_help (e.g. tblHelp) and modify your code to insert to it, e.g.
Select @sql = @sql + N'INSERT INTO tblHelp EXEC sp_help ''['+TABLE_SCHEMA+N'].['+TABLE_NAME+N']'';'
From INFORMATION_SCHEMA.TABLES
the problem is , at least for a table , SEVEN different result sets, all with different # of columns and data types are returned; so you would really need to do sp_helptext sp_help, and do the work it was doing yourself, and stick the results into seven differently shaped tables for the results;
alternatively, you could do something like this,a nd jsut return a ton of rows as varchar(maxes)
i fiddled with this in another post for someone who wanted to do sp_help on a web interface:
note i did not finish this/flesh it out 100%, but rather threw a model out there in the original post.
--sp_help Tally
--sp_helpweb Tally
--sp_help sp_helpweb
--sp_helpweb sp_helpweb
--select object_id('sp_helpweb')
ALTER PROCEDURE dbo.SP_HELPWEB @objname NVARCHAR(776) = NULL -- object name we're after
AS
BEGIN
-- PRELIMINARY
SET nocount ON
DECLARE
@dbname SYSNAME,
@no VARCHAR(35),
@yes VARCHAR(35),
@none VARCHAR(35)
SELECT @no = 'no',
@yes = 'yes',
@none = 'none'
DECLARE @Results TABLE(
ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
ResultsText VARCHAR(8000))
IF @objname IS NULL
BEGIN
INSERT INTO @Results
(ResultsText)
SELECT CONVERT(CHAR(50), 'A Table or Object must be specificed for sp_helpweb.')
SELECT *
FROM @Results
RETURN( 0 )
END
-- Make sure the @objname is local to the current database.
SELECT @dbname = PARSENAME(@objname, 3)
IF @dbname IS NULL
SELECT @dbname = DB_NAME()
ELSE
IF @dbname <> DB_NAME()
BEGIN
INSERT INTO @Results
(ResultsText)
SELECT 'the @objname must be local to the current database, cross database queries or 3 part naming conventions are not permitted for sp_helpweb.'
SELECT *
FROM @Results
RETURN( 1 )
END
-- @objname must be either sysobjects or systypes: first look in sysobjects
DECLARE @objid INT
DECLARE @sysobj_type CHAR(2)
SELECT @objid = object_id,
@sysobj_type = type
FROM sys.all_objects
WHERE object_id = OBJECT_ID(@objname)
-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
IF @objid IS NULL
BEGIN
-- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
SELECT @objid = TYPE_ID(@objname)
-- IF NOT IN SYSTYPES, GIVE UP
IF @objid IS NULL
BEGIN
INSERT INTO @Results
(ResultsText)
SELECT 'the object_id for ' + @objname + ' was not found and connot be described via sp_helpweb.'
SELECT *
FROM @Results
RETURN( 1 )
END
END
-- FOUND IT IN SYSOBJECT, SO GIVE OBJECT INFO
INSERT INTO @Results
(ResultsText)
SELECT CONVERT(CHAR(50), 'Name') + '|' + CONVERT(CHAR(50), 'Owner') + '|' + CONVERT(CHAR(50), 'Type') + CONVERT(CHAR(50), 'Created_datetime')
INSERT INTO @Results
(ResultsText)
SELECT CONVERT(CHAR(50), o.name) + '|' + CONVERT(CHAR(50), USER_NAME(OBJECTPROPERTY( object_id, 'ownerid'))) + '|' + CONVERT(CHAR(50), SUBSTRING(v.name, 5, 31)) + '|' + CONVERT(CHAR(50), o.create_date)
FROM sys.all_objects o,
master.dbo.spt_values v
WHERE o.object_id = @objid
AND o.type = SUBSTRING(v.name, 1, 2) COLLATE database_default
AND v.type = 'O9T'
ORDER BY
o.name ASC
INSERT INTO @Results
(ResultsText)
SELECT REPLICATE('-', 100)
-- DISPLAY COLUMN IF TABLE / VIEW
IF EXISTS (SELECT *
FROM sys.all_columns
WHERE object_id = @objid)
BEGIN
-- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
DECLARE @numtypes NVARCHAR(80)
SELECT @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'
-- INFO FOR EACH COLUMN
INSERT INTO @Results
(ResultsText)
SELECT CONVERT(CHAR(50), 'Column_name') + '|' + CONVERT(CHAR(16), 'Type') + '|' + CONVERT(CHAR(8), 'Computed') + '|' + CONVERT(CHAR(8), 'Length') + '|' + CONVERT(CHAR(8), 'Prec') + '|' + CONVERT(CHAR(8), 'Scale') + '|' + CONVERT(CHAR(8), 'Nullable') + '|' + CONVERT(CHAR(18), 'TrimTrailingBlanks') + '|' + CONVERT(CHAR(20), 'FixedLenNullInSource') + '|' + CONVERT(CHAR(20), 'Collation')
INSERT INTO @Results
(ResultsText)
SELECT CONVERT(CHAR(50), name) + '|' + CONVERT(CHAR(16), TYPE_NAME(user_type_id)) + '|' + CONVERT(CHAR(8), CASE WHEN COLUMNPROPERTY(object_id, name, 'IsComputed') = 0 THEN @no ELSE @yes END) + '|' + CONVERT(CHAR(8), CONVERT(INT, max_length)) + '|' + CONVERT(CHAR(8), CASE WHEN CHARINDEX(TYPE_NAME(system_type_id), @numtypes) > 0 THEN CONVERT(CHAR(5), COLUMNPROPERTY(object_id, name, 'precision')) ELSE ' ' END) + '|' + CONVERT(CHAR(8), CASE WHEN CHARINDEX(TYPE_NAME(system_type_id), @numtypes) > 0 THEN CONVERT(CHAR(5), ODBCSCALE(system_type_id, scale)) ELSE ' ' END) + '|' + CONVERT(CHAR(8), CASE WHEN is_nullable = 0 THEN @no ELSE @yes END) + '|' + CONVERT(CHAR(18), CASE COLUMNPROPERTY(object_id, name, 'UsesAnsiTrim') WHEN 1 THEN @no WHEN 0 THEN @yes ELSE '(n/a)' END) + '|' + CONVERT(CHAR(20), CASE WHEN TYPE_NAME(system_type_id) NOT IN ('varbinary', 'varchar', 'binary', 'char') THEN '(n/a)' WHEN is_nullable = 0 THEN @no ELSE @yes END) + '|' +
CONVERT(CHAR(20), ISNULL(collation_name, 'NULL'))
FROM sys.all_columns
WHERE object_id = @objid
INSERT INTO @Results
(ResultsText)
SELECT REPLICATE('-', 100)
END --if exists sys.columns
IF @sysobj_type IN ( 'S ', 'U ', 'V ', 'TF' )
AND @objid > 0
BEGIN
DECLARE @colname SYSNAME
SELECT @colname = COL_NAME(@objid, column_id)
FROM sys.identity_columns
WHERE object_id = @objid
-- IDENTITY COLUMN?
INSERT INTO @Results
(ResultsText)
SELECT CONVERT(CHAR(27), 'Identity') + '|' + CONVERT(CHAR(4), 'Seed') + '|' + CONVERT(CHAR(9), 'Increment') + '|' + CONVERT(CHAR(20), 'Not For Replication')
INSERT INTO @Results
(ResultsText)
SELECT +CONVERT(CHAR(27), ISNULL(@colname, 'No identity column defined.')) + '|' + CONVERT(CHAR(4), IDENT_SEED(@objname)) + '|' + CONVERT(CHAR(9), IDENT_INCR(@objname)) + '|' + CONVERT(CHAR(20), COLUMNPROPERTY(@objid, @colname, 'IsIDNotForRepl'))
INSERT INTO @Results
(ResultsText)
SELECT REPLICATE('-', 100)
-- ROWGUIDCOL?
SELECT @colname = NULL
SELECT @colname = name
FROM sys.columns
WHERE object_id = @objid
AND is_rowguidcol = 1
INSERT INTO @Results
(ResultsText)
SELECT CONVERT(CHAR(30), 'RowGuidCol')
INSERT INTO @Results
(ResultsText)
SELECT CONVERT(CHAR(30), ISNULL(@colname, 'No rowguidcol column defined.'))
INSERT INTO @Results
(ResultsText)
SELECT REPLICATE('-', 100)
IF EXISTS (SELECT *
FROM sys.all_parameters
WHERE object_id = @objid)
BEGIN
INSERT INTO @Results
(ResultsText)
SELECT CONVERT(CHAR(50), 'Parameter_name') + '|' + CONVERT(CHAR(16), 'Type') + '|' + CONVERT(CHAR(8), 'Length') + '|' + CONVERT(CHAR(16), 'Prec') + '|' + CONVERT(CHAR(16), 'Scale') + '|' + CONVERT(CHAR(16), 'Param_order') + '|' + CONVERT(CHAR(50), 'Collation')
INSERT INTO @Results
(ResultsText)
SELECT CONVERT(CHAR(50), name) + '|' + CONVERT(CHAR(16), TYPE_NAME(user_type_id)) + '|' + CONVERT(CHAR(8), max_length) + '|' + CONVERT(CHAR(16), CASE WHEN TYPE_NAME(system_type_id) = 'uniqueidentifier' THEN PRECISION ELSE ODBCPREC(system_type_id, max_length, PRECISION) END) + '|' + CONVERT(CHAR(16), ISNULL(ODBCSCALE(system_type_id, scale), '')) + '|' + CONVERT(CHAR(16), parameter_id) + '|' + CONVERT(CHAR(50), CONVERT(SYSNAME, CASE WHEN system_type_id IN (35, 99, 167, 175, 231, 239) THEN SERVERPROPERTY('collation') ELSE '' END))
FROM sys.all_parameters
WHERE object_id = @objid
INSERT INTO @Results
(ResultsText)
SELECT REPLICATE('-', 100)
END --parameters
END --if object that has idents or rowguids
--final results
SELECT *
FROM @Results
RETURN( 0 )
END --PROC
Lowell
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply